Conditional split variable

Question

Post reply

Conditional split variable

as_1234

SSCrazy

Points: 2859
More actions
February 12, 2020 at 11:07 pm

#3725412
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

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

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

palandri Old Hand Points: 387 More actions · Answer 1

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

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

as_1234 SSCrazy Points: 2859 More actions · Answer 2

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

palandri Old Hand Points: 387 More actions · Answer 3

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.

as_1234 SSCrazy Points: 2859 More actions · Answer 4

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.

as_1234 SSCrazy Points: 2859 More actions · Answer 5

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: 387 More actions · Answer 6

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 years, 9 months ago by palandri.

as_1234 SSCrazy Points: 2859 More actions · Answer 7

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")