Case Statement with multiple conditions and excluding null values

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:
I need it to look like this:
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
-
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 toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.2
Answers
-
Try just doing this:
IFNULL(column2,column1)
**Check out my Domo Tips & Tricks Videos
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
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.
0 -
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 toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
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?
0 -
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 toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.2 -
That worked, thank you!
0
Categories
- 10.5K All Categories
- 3 Connect
- 913 Connectors
- 250 Workbench
- 458 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 476 Datasets
- 183 Visualize
- 249 Beast Mode
- 2.1K Charting
- 11 Variables
- 16 Automate
- 354 APIs & Domo Developer
- 88 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 245 Distribute
- 62 Domo Everywhere
- 242 Scheduled Reports
- 20 Manage
- 41 Governance & Security
- 168 Product Ideas
- 1.2K Ideas Exchange
- 9 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive