SSIS Derived column IF..ELSE condition
I have a column WEEK which is VARCHAR and has values like 201112,201135 in which last two characters represend the week no...so I need to create a derived column NEW_WEEK in such a way that
if (cast( SUBSTRING( WEEK,5,2) as int)>=1 AND cast( SUBSTRING( WEEK,5,2) as int)<=17)
Then NEW_WEEK=( cast( SUBSTRING( WEEK,5,2) as int)+17)
ELSE (cast( SUBSTRING( WEEK,5,2) as int)-17)
I am using following expression..
(((DT_I4) SUBSTRING( WEEK,5,2)>=1) && ((DT_I4) SUBSTRING( WEEK,5,2)<=17))?((DT_I4) SUBSTRING( WEEK,5,2)+35):((DT_I4) SUBSTRING( WEEK,5,2)-17))
Can somebody tell me whats wrong in it?
I also tried..
IIF((DT_I4) [SUBSTRING( CALWEEK,5,2)]>=1 && (DT_I4) [SUBSTRING( CALWEEK,5,2)]<=17) then ((DT_I4) [SUBSTRING( CALWEEK,5,2)]+35) else ((DT_I4) [SUBSTRING( CALWEEK,5,2)]-17))
its also not working :(
July 30th, 2011 3:39am
what is the error you are getting??My Blog |
Ask Me |
Free Windows Admin Tool Kit Click here and download it now
July 30th, 2011 4:13am
I have a column WEEK which is VARCHAR and has values like 201112,201135 in which last two characters represend the week no...so I need to create a derived column NEW_WEEK in such a way that
if (cast( SUBSTRING( WEEK,5,2) as int)>=1 AND cast( SUBSTRING( WEEK,5,2) as int)<=17)
Then NEW_WEEK=( cast( SUBSTRING( WEEK,5,2) as int)+17)
ELSE (cast( SUBSTRING( WEEK,5,2) as int)-17)
I am using following expression..
(((DT_I4) SUBSTRING( WEEK,5,2)>=1) && ((DT_I4) SUBSTRING( WEEK,5,2)<=17))?((DT_I4) SUBSTRING( WEEK,5,2)+35):((DT_I4) SUBSTRING( WEEK,5,2)-17))
Can somebody tell me whats wrong in it?
I also tried..
IIF((DT_I4) [SUBSTRING( CALWEEK,5,2)]>=1 && (DT_I4) [SUBSTRING( CALWEEK,5,2)]<=17) then ((DT_I4) [SUBSTRING( CALWEEK,5,2)]+35) else ((DT_I4) [SUBSTRING( CALWEEK,5,2)]-17))
its also not working :(
(DT_I4) SUBSTRING( WEEK,5,2) >=1 && (DT_I4) SUBSTRING( WEEK,5,2) <= 17 ? (DT_I4) (SUBSTRING( WEEK,5,2))+ 35 : (DT_I4) (SUBSTRING( WEEK,5,2))-17
I made a modification different from this expression which gave an error and now adjusted to the expression above. When i tired to paste this in Derived column expression, I am not able to do so due to weird behavior and I tried a lot but in vain, hope this
expression resolves the issue.Happy to help! Thanks. Regards and good Wishes, Deepak. In a revamp of my Blog!
July 30th, 2011 4:51am
I have a column WEEK which is VARCHAR and has values like 201112,201135 in which last two characters represend the week no...so I need to create a derived column NEW_WEEK in such a way that
if (cast( SUBSTRING( WEEK,5,2) as int)>=1 AND cast( SUBSTRING( WEEK,5,2) as int)<=17)
Then NEW_WEEK=( cast( SUBSTRING( WEEK,5,2) as int)+17)
ELSE (cast( SUBSTRING( WEEK,5,2) as int)-17)
I am using following expression..
(((DT_I4) SUBSTRING( WEEK,5,2)>=1) && ((DT_I4) SUBSTRING( WEEK,5,2)<=17))?((DT_I4) SUBSTRING( WEEK,5,2)+35):((DT_I4) SUBSTRING( WEEK,5,2)-17))
Can somebody tell me whats wrong in it?
I also tried..
IIF((DT_I4) [SUBSTRING( CALWEEK,5,2)]>=1 && (DT_I4) [SUBSTRING( CALWEEK,5,2)]<=17) then ((DT_I4) [SUBSTRING( CALWEEK,5,2)]+35) else ((DT_I4) [SUBSTRING( CALWEEK,5,2)]-17))
its also not working :(
(DT_I4) SUBSTRING( WEEK,5,2) >=1 && (DT_I4) SUBSTRING( WEEK,5,2) <= 17 ? (DT_I4) (SUBSTRING( WEEK,5,2))+ 35 : (DT_I4) (SUBSTRING( WEEK,5,2))-17
I made a modification different from this expression which gave an error and now adjusted to the expression above. When i tired to paste this in Derived column expression, I am not able to do so due to weird behavior and I tried a lot but in vain, hope this
expression resolves the issue.
Happy to help! Thanks. Regards and good Wishes, Deepak. In a revamp of my Blog!
It still does not work.. :(
Free Windows Admin Tool Kit Click here and download it now
July 30th, 2011 5:08am
HI rockstar,
This expression worked without any error (no red text), I copide in bits and pieces and it worked ? Do you get any error after the run?Happy to help! Thanks. Regards and good Wishes, Deepak. In a revamp of my Blog!
July 30th, 2011 6:02am
IT ACTUALLY DID WORK..THANKS YOU MATE..APPRECIATE YOUR HELP :)
Free Windows Admin Tool Kit Click here and download it now
August 27th, 2011 1:00pm
Great! Really happy to help!Happy to help! Thanks. Regards and good Wishes, Deepak. In a revamp of my Blog!
August 28th, 2011 3:35am