Conditional split variable

  • as1981

    SSCrazy

    Points: 2732

    All,

    I'm trying to use a variable in a conditional split condition. I tried a few combinations similar to the following:

    CreationDate >= (DT_DBDATE)"01/01" + [@$Package::Year] && CreationDate < (DT_DBDATE)"01/02/2019"

    The year parameter contains a four digit year. Would someone mind advising the correct syntax? I did some searching but with no success.

    Thanks

  • palandri

    Old Hand

    Points: 318

    Do you maybe need a "/"  and perhaps parens:

    ( "01/01" + "/" + [@$Package::Year] )

  • as1981

    SSCrazy

    Points: 2732

    Thank you for the suggestion. I tried both of those and they didn't work.

  • palandri

    Old Hand

    Points: 318

    I didn't actually test that, just noticed there was a delimiter missing.  I tested this in a derived column:

    (DT_DBDATE)("1999" + "-10-11")

    and it produced a value of 10/11/1999 in a data viewer.

    It should work for you as long as your [@$Package::Year] variable contains a valid four digit character year.  If it's not character, you'll probably need to cast it, so it can be prepended to the character month and day.

     

     

  • as1981

    SSCrazy

    Points: 2732

    Thank you for your help. The editor accepts that value but the filter doesn't work. I think it is something to do with date format. I'll will post more details, including any solution I find, when I have more time.

     

  • as1981

    SSCrazy

    Points: 2732

    Unfortunately I'm not making much progress solving this.

    The following works fine:

    CreationDate >= (DT_DBDATE)("01/02/2019") && CreationDate < (DT_DBDATE)("01/03/2019")

    The following is accepted but doesn't match any records:

    CreationDate >= (DT_DBDATE)(01 / 01 / @[$Package::Year]) && CreationDate < (DT_DBDATE)(01 / 02 / @[$Package::Year])

    I think it has something to do with US/UK date format but I've tried various combinations of the separators / and - and different orders of month, year and date with no success. I've also tried a few combinations of casting and functions such as:

    CreationDate >= (DT_DBDATE)(day(01) / month(01) / @[$Package::Year]) && CreationDate < (DT_DBDATE)(01 / 02 / @[$Package::Year])

    However it doesn't accept them.

    I would appreciate any pointers in the right direction.

  • palandri

    Old Hand

    Points: 318

    The dates created in the second expression shown in your last post may not be what  you expect. When I tried this one substituting a hard coded year:  (DT_DBDATE)(01 / 01 / 2019)

    the date returned is 12/30/1899

    (01 / 01 / 2019) may be interpreted as two divisions.

    I stick with the hyphen notation when assembling date.  Maybe try: (DT_DBDATE)("@[$Package::Year] + "-01-01")

    If @[$Package::Year] is numeric, you'll need to cast it char first.

    It's tough to create an expression if you don't know the values going into it are legit.  You might try creating derived columns for these expressions and checking them in a viewer:

    • @[$Package::Year]
    • (DT_DBDATE)(01 / 01 / @[$Package::Year])
    • (DT_DBDATE)((DT_WSTR,4)2019 + "-01-01")
    • (DT_DBDATE)((DT_WSTR,4)@[$Package::Year] + "-01-01")

     

     

     

     

    • This reply was modified 4 months, 3 weeks ago by  palandri.
  • as1981

    SSCrazy

    Points: 2732

    Thank you for your help. It case it helps anyone else the following works:

    CreationDate >= (DT_DBDATE)((DT_WSTR,4)@[$Package::Year] + (DT_WSTR,6)"-01-01") && CreationDate < (DT_DBDATE)((DT_WSTR,4)@[$Package::Year] + (DT_WSTR,6)"-02-01")

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply