Expression with multiple IFs

  • 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

  • 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

    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