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
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 310 Workbench
- 7 Cloud Amplifier
- 9 Federated
- 3K Transform
- 114 SQL DataFlows
- 654 Datasets
- 2.2K Magic ETL
- 4K Visualize
- 2.5K Charting
- 796 Beast Mode
- 78 App Studio
- 44 Variables
- 757 Automate
- 188 Apps
- 480 APIs & Domo Developer
- 72 Workflows
- 17 DomoAI
- 40 Predict
- 17 Jupyter Workspaces
- 23 R & Python Tiles
- 408 Distribute
- 119 Domo Everywhere
- 279 Scheduled Reports
- 10 Software Integrations
- 141 Manage
- 137 Governance & Security
- 8 Domo Community Gallery
- 47 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 114 Community Announcements
- 4.8K Archive