Bucket analysis in SSIS Derived column

Question

I have a do a bucket analysis of some days calculation in SSIS staging

please help me in fixing this

DAYS <=90 = 0-3 Months
DAYS >=91 &&  DAYS <=180 = 3-6 Months
else > 6 Months

which I tried to frame like below but not working, and i am very new to this, please suggest

(DAYS <="90") ? "0-3 Months": ((DAYS >= "91") &&  (DAYS <= "180") ? "3-6 Months": "> 6 Months")

Hi EzLo did you mean like this ([DA_BUCKET_DAYS]<=90) ? "0-3 Months": ([DA_BUCKET_DAYS]>= 91) && [DA_BUCKET_DAYS] <= 180) ? "3-6 Months": "> 6 Months" — Manoj, Sep 19 '19 at 12:17
Yes, that should be OK. If it still doesn't work that way then add some sample data so we can troubleshoot. — EzLo, Sep 19 '19 at 12:30
Hi EzLo, Thank you I did that and it still came up with error, but ([DA_BUCKET_DAYS]<=90) ? "0-3 Months": ([DA_BUCKET_DAYS]>= 91) && [DA_BUCKET_DAYS] <= 180) ? "3-6 Months": "> 6 Months" removed the ( ) and it worked — Manoj, Sep 19 '19 at 14:00

score 0 · Accepted Answer · answered Sep 19 '19 at 14:56

Your comparisons should be against numbers, not strings (they shouldn't have the enclosing "). Other than that, the expression seems OK.

Make sure that DAYS is of an integer type and not STR (or WSTR) because comparing numbers behave different than comparing strings. You can try casting DAYS to I8 before the comparison if its a string type.

score 0 · Answer 2 · answered Sep 23 '19 at 14:36

Please go this way ,it might help you in this case . Same thing I have already used in one of My project same logic.

LoadDate== DATEDDIFF(dd,LoadDate,Getdate())<=90 ? "0-3 Months DAYS" : LoadDate== DATEDDIFF(dd,LoadDate,Getdate())>91 ? "0-6 Months DAYS" : "Other"

Bucket analysis in SSIS Derived column

2 Answers2