Case Statement with multiple conditions and excluding null values

user028686
user028686 Member
edited March 2023 in Datasets

I am trying to create a case statement where I have a new column that replaces a value in column1 with a value from column 2 but only if column 2 is not null. If it is null, then the value in column1 needs to remain the same.

Sample table:

image.png

I need it to look like this:

image.png


So "Account A" is replaced with "name" from column 2 when it is available.

I have tried this but it is not working:

(CASE 

 when

`Column2`is not null 

and `Column1`= 'Account A'

then `Column2`

else `Column1`

End)

What am I doing wrong?

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    If you're only wanting this to process for Account A, I would a nested case statement like this:

    (CASE WHEN TRIM(`column1`) = 'Account A' THEN
(CASE WHEN TRIM(`column2`) = '' THEN `column1`
ELSE `column2`
END)
ELSE `column1`
END)


    **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

  • MarkSnodgrass
    MarkSnodgrass Coach

    Try just doing this:

    IFNULL(column2,column1)

    **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.
  • user028686
    user028686 Member

    Thank you. That does not seem to give me the correct result because if column 2 is blank, then it is showing blank in the new column.

  • MarkSnodgrass
    MarkSnodgrass Coach

    Blanks and nulls are not the same, so the ifnull function would not help with this data. I would try this to look for blanks:

    (CASE WHEN TRIM(`column2`) = '' THEN `column1`
ELSE `column2`
END)

    The trim function removes any spaces that may be in the data and then you can evaluate it to see if it is blank.

    Hope this helps.

    **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.
  • user028686
    user028686 Member

    Thank you! I am getting closer...the first two rows are displaying correctly now, but now Account B is also being replaced with the name field, so how do I prevent that from happening?

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    If you're only wanting this to process for Account A, I would a nested case statement like this:

    (CASE WHEN TRIM(`column1`) = 'Account A' THEN
(CASE WHEN TRIM(`column2`) = '' THEN `column1`
ELSE `column2`
END)
ELSE `column1`
END)


    **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.
  • user028686
    user028686 Member

    That worked, thank you!

Categories