Using a simple case when function not working
Hi so I am trying to use an if-then statement in beast mode but it is not giving me the correct output.
The field I am using in this code is product status and these are following values inside it.
What I am trying to do is bucket the values into two buckets one which is repeat and one which is new.
The code that I am trying to use is this
CASE WHEN PRODUCTSTATUS
= 'R' OR 'Repeat' THEN 'Repeat' ELSE 'New' END
When I use this define function I only get the value for "new" not "repeat" not sure what I am doing wrong but below is the example
I need R and Repeat to come as repeat and anything else to come up as new if anyone can help me with this code.
Best Answer
-
You could try an IN statement instead of OR, something like:
CASE WHEN TRIM(UPPER((`PRODUCTSTATUS`)) IN ('R','REPEAT') THEN 'Repeat' ELSE 'New' END
I'm setting it to upper case and trimming whitespaces just to cover the possibility of the data not being clean, which is what I could guess based on the output you currently show in the screenshot.
1
Answers
-
It's treating your 'Repeat' clause in your logical expression as TRUE as it's not 0 value. You need to compare the value again to your field:
CASE WHEN `PRODUCTSTATUS` = 'R' OR `PRODUCTSTATUS` = 'Repeat' THEN 'Repeat' ELSE 'New' END
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
Hi Grant,
Thank you for the quick response seems like that did not fix the issue here is a screenshot of the results:
any other variation of the code I can try
0 -
You could try an IN statement instead of OR, something like:
CASE WHEN TRIM(UPPER((`PRODUCTSTATUS`)) IN ('R','REPEAT') THEN 'Repeat' ELSE 'New' END
I'm setting it to upper case and trimming whitespaces just to cover the possibility of the data not being clean, which is what I could guess based on the output you currently show in the screenshot.
1
Categories
- All Categories
- 1.4K Product Ideas
- 1.4K Ideas Exchange
- 1.4K Connect
- 1.1K Connectors
- 278 Workbench
- 4 Cloud Amplifier
- 4 Federated
- 2.7K Transform
- 89 SQL DataFlows
- 557 Datasets
- 2K Magic ETL
- 3.3K Visualize
- 2.3K Charting
- 571 Beast Mode
- 11 App Studio
- 28 Variables
- 579 Automate
- 141 Apps
- 414 APIs & Domo Developer
- 23 Workflows
- 1 DomoAI
- 28 Predict
- 12 Jupyter Workspaces
- 16 R & Python Tiles
- 352 Distribute
- 92 Domo Everywhere
- 258 Scheduled Reports
- 2 Software Integrations
- 92 Manage
- 89 Governance & Security
- 9 Product Release Questions
- Community Forums
- 42 Getting Started
- 28 Community Member Introductions
- 88 Community Announcements
- 4.8K Archive