March 26, 2020 at 8:09 pm
I am trying to create an expression to derive a data column value based off two other data columns. In English
If VARA is greater than three characters long an the first character is "1" then VARB = VARC + ".A" + the last two characters of VARA
ELSE
If VARA is greater than three characters long an the first character is "2" then VARB = VARC + ".B" + the last two characters of VARA
ELSE
If VARA is greater than three characters long an the first character is "3" then VARB = VARC + ".C" + the last two characters of VARA
ELSE VARD
Here is my expression but it is only converting the VARA values that start with 1. Can anyone tell me what is wrong with the expression? I can probably do this using a SQL script but wanted to do it in a derived column container in my data flow. Any help is appreciated!
LEN(TRIM(VARA)) > 3 && LEFT(VARA,1) == "1" ? (DT_STR,11,1252)RTRIM(VARB) + ".A" + RIGHT(RTRIM(VARA),2) : LEN(TRIM(VARA)) > 3 && LEFT(VARA,1) == "2" ? (DT_STR,11,1252)RTRIM(VARB) + ".B" + RIGHT(RTRIM(VARA),2) : LEN(TRIM(VARA)) > 3 && LEFT(VARA,1) == "3" ? (DT_STR,11,1252)RTRIM(VARB) + ".C" + RIGHT(RTRIM(VARA),2) : (DT_STR,11,1252)VARD
March 26, 2020 at 8:35 pm
If it were me, I think I'd resort to a script component, as there are too many nested IFs for my liking.
However, things could be made much easier on the eye if you create a few derived columns first. Something like:
Then, your expression (the overall syntax/logic looks fine) becomes simpler:
LEN(VARAx) > 3 && LEFT(VARAx,1) == "1" ?
VARBx + ".A" + RIGHT(VARAx,2) :
LEN(VARAx) > 3 && LEFT(VARAx,1) == "2" ?
VARBx + ".B" + RIGHT(VARAx,2) :
LEN(VARAx) > 3 && LEFT(VARAx,1) == "3" ?
VARBx + ".C" + RIGHT(VARAx,2) :
VARDx
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply