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
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply