Expression with multiple IFs

  • karen.ferrara

    Say Hey Kid

    Points: 703

    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

  • Phil Parkin

    SSC Guru

    Points: 244133

    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:

    • (DT_STR,11,1252)TRIM(VARA)
    • (DT_STR,11,1252)TRIM(VARB)
    • (DT_STR,11,1252)TRIM(VARD)

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Viewing 2 posts - 1 through 2 (of 2 total)

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