Case When statement

user082291
user082291 Member
in Charting

Is it possible to use contains instead of = in the Case When statement

CASE when 'Amount'=1 then 10 else 0 End

For example: Case when 'Reason' contains 'Retal' then 'Retaliation' Else 'Other' End

we have many different variations of Retaliation statements and don't want to spell each one out in a case when statement

Fear of Retaliation, I feel retaliated, Fear retaliated against, etc.

want to combine all the variations into one bucket of Retaliation.

Thank you, Jill Bowers

Best Answers

  • user09644
    user09644 Member
    edited February 2021 Answer ✓

    @user082291 You'll want to use wildcard character to achieve this. I like to wrap the upper() function to remove casing variability.

    CASE WHEN UPPER('REASON') LIKE ('RETAL%') then 'Retaliation' ELSE 'Other' END

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    @user09644 is spot on. Additionally, adding the % before the word will allow you to look for that word anywhere in the phrase. If you only have the % at the end, then you are requiring that your phrase starts with the word retail. Using '%RETAIL%' will allow it to search for that word anywhere in the phrase.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • user09644
    user09644 Member
    edited February 2021 Answer ✓

    @user082291 You'll want to use wildcard character to achieve this. I like to wrap the upper() function to remove casing variability.

    CASE WHEN UPPER('REASON') LIKE ('RETAL%') then 'Retaliation' ELSE 'Other' END

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    @user09644 is spot on. Additionally, adding the % before the word will allow you to look for that word anywhere in the phrase. If you only have the % at the end, then you are requiring that your phrase starts with the word retail. Using '%RETAIL%' will allow it to search for that word anywhere in the phrase.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • user082291
    user082291 Member

    Thank you for the prompt answer.  This is what I was looking for.

Categories