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 to any 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 to any 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 to any 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 to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.2 -
That worked, thank you!
0
Categories
- All Categories
- 1.4K Product Ideas
- 1.4K Ideas Exchange
- 1.4K Connect
- 1.2K Connectors
- 284 Workbench
- 4 Cloud Amplifier
- 4 Federated
- 2.9K Transform
- 88 SQL DataFlows
- 554 Datasets
- 2.2K Magic ETL
- 3.3K Visualize
- 2.3K Charting
- 562 Beast Mode
- 9 App Studio
- 27 Variables
- 577 Automate
- 140 Apps
- 414 APIs & Domo Developer
- 22 Workflows
- 1 DomoAI
- 28 Predict
- 12 Jupyter Workspaces
- 16 R & Python Tiles
- 350 Distribute
- 90 Domo Everywhere
- 258 Scheduled Reports
- 2 Software Integrations
- 91 Manage
- 88 Governance & Security
- 9 Product Release Questions
- Community Forums
- 42 Getting Started
- 28 Community Member Introductions
- 85 Community Announcements
- 4.8K Archive