Conditional split variable

  • 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

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

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

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

  • 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.

     

     

  • 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.

     

  • 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.

  • 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 1 year, 5 months ago by  palandri.
  • 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