case when something then blank

meadow_ryan
meadow_ryan Member
in Magic ETL

How would I write a case if I have a field 'link agency id' to the following, if the the link agency ID = BKR display blank or empty cell else display whateve the link agency id is

 

CASE

when `Link Agency ID` = 'BKR' then ''
else `Link Agency ID`

END

Best Answers

  • Nick_Bertz
    Nick_Bertz Member
    Answer ✓

    Just in case 'BKR' is something link ' BKR ' or ' BKR' or 'BKR ', try using:

     

    CASE
    when `Link Agency ID` LIKE '%BKR%' then ''
    else `Link Agency ID`
    END

  • Valiant
    Valiant Coach
    Answer ✓

    Definitely odd. Perhaps there are phantom characters? 

     

    Try this:

    CASE
    when `Link Agency ID` LIKE '%BKR%' then ''
    else `Link Agency ID`
    END

    Otherwise, I might try that beast mode on a clean card, make sure there isnt' a weird browser issue.

Answers

  • Valiant
    Valiant Coach

    Here you go:

     

    CASE WHEN `Link Agency ID` <> 'BKR' 
               THEN `Link Agency ID` 
    END

    By omitting the ELSE, anything that doesn't match (not equal to BKR, so BKR) will get assigned a NULL value.

     

    Let me know if that wasn't what you were looking for.


    Sincerely,

    Valiant

     

    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.

  • meadow_ryan
    meadow_ryan Member

    that didn't seem to work, so what I'm looking for is if link agency id = BKR then show a blank value, else show whatever link agency id is

     

    thanks

  • Nick_Bertz
    Nick_Bertz Member

    Hi,

     

    Try This - 

     

    CASE

    when `Link Agency ID` LIKE 'BKR' then ''
    else `Link Agency ID`

    END

     

    Thanks!

  • meadow_ryan
    meadow_ryan Member

    that didn't work either

  • Valiant
    Valiant Coach

    What card type are you using?

     

    Can you provide more detail around the error/result you are seeing?

  • meadow_ryan
    meadow_ryan Member

    just a table card....attached is what is showing when I have

     

    CASE
    when `Link Agency ID` LIKE 'BKR' then ''
    else `Link Agency ID`
    END

  • Nick_Bertz
    Nick_Bertz Member
    Answer ✓

    Just in case 'BKR' is something link ' BKR ' or ' BKR' or 'BKR ', try using:

     

    CASE
    when `Link Agency ID` LIKE '%BKR%' then ''
    else `Link Agency ID`
    END

  • Valiant
    Valiant Coach
    Answer ✓

    Definitely odd. Perhaps there are phantom characters? 

     

    Try this:

    CASE
    when `Link Agency ID` LIKE '%BKR%' then ''
    else `Link Agency ID`
    END

    Otherwise, I might try that beast mode on a clean card, make sure there isnt' a weird browser issue.

  • meadow_ryan
    meadow_ryan Member

    thanks that worked

Categories