Case statement not working
Please help with my ETL formular.
I'm not sure why my case only works for…
WHEN AdditionalRecruiter
= '' and RecruiterInOrder
= 1 THEN 'Unassigned Owner'
WHEN STR_CONTAINS(AdditionalRecruiter
,',') THEN AllRecruiter
WHEN AllRecruiter
IS NULL THEN 'N/C'
Below is the code I'm using to populate the 'RecruiterOutcome' column. I'm using N/A, N/B, N/C, and N/D to differentiate between cases. The goal is to fill the 'RecruiterOutcome' column with any flag in the blank rows, so that I can filter them out later.
CASE WHEN AdditionalRecruiter
= '' and RecruiterInOrder
= 1 THEN 'Unassigned Owner'
WHEN STR_CONTAINS(AdditionalRecruiter
,',') THEN AllRecruiter
WHEN STR_CONTAINS(AdditionalRecruiter
,',') and AllRecruiter
IS NULL THEN 'N/A'
WHEN STR_CONTAINS(AdditionalRecruiter
,',') and AllRecruiter
= '' THEN 'N/B'
WHEN AllRecruiter
IS NULL THEN 'N/C'
WHEN AllRecruiter
= '' THEN 'N/D'
ELSE AllRecruiter
END
Best Answers
-
Case statements are evaluated from the top down and will stop when they find a condition that's true.
Because you have
WHEN STR_CONTAINS(
AdditionalRecruiter
,',') THENAllRecruiter
At the top of your case statement the other two below it won't ever get evaluated. Put the N/A and N/B conditions above this to have them evaluated first.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**3 -
@GrantSmith Thank you for your quick response. Your suggestion works well! I also realized that I made things too complicated. My formula should be as simple as the below code, then add a filter after to filter out the NULL rows in the 'RecruiterOutcome' column.
Problem solved!!! 😀
CASE WHEN
AdditionalRecruiter
= '' andRecruiterInOrder
= 1 THEN 'Unassigned Owner'
WHEN STR_CONTAINS(AdditionalRecruiter
,',') THENAllRecruiter
ELSEAllRecruiter
END0
Answers
-
Case statements are evaluated from the top down and will stop when they find a condition that's true.
Because you have
WHEN STR_CONTAINS(
AdditionalRecruiter
,',') THENAllRecruiter
At the top of your case statement the other two below it won't ever get evaluated. Put the N/A and N/B conditions above this to have them evaluated first.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**3 -
@GrantSmith Thank you for your quick response. Your suggestion works well! I also realized that I made things too complicated. My formula should be as simple as the below code, then add a filter after to filter out the NULL rows in the 'RecruiterOutcome' column.
Problem solved!!! 😀
CASE WHEN
AdditionalRecruiter
= '' andRecruiterInOrder
= 1 THEN 'Unassigned Owner'
WHEN STR_CONTAINS(AdditionalRecruiter
,',') THENAllRecruiter
ELSEAllRecruiter
END0
Categories
- All Categories
- Product Ideas
- 2.1K Ideas Exchange
- Connect
- 1.3K Connectors
- 309 Workbench
- 7 Cloud Amplifier
- 10 Federated
- Transform
- 663 Datasets
- 119 SQL DataFlows
- 2.3K Magic ETL
- 823 Beast Mode
- Visualize
- 2.6K Charting
- 86 App Studio
- 46 Variables
- Automate
- 194 Apps
- 483 APIs & Domo Developer
- 87 Workflows
- 23 Code Engine
- AI and Machine Learning
- 23 AI Chat
- 4 AI Projects and Models
- 18 Jupyter Workspaces
- Distribute
- 117 Domo Everywhere
- 283 Scheduled Reports
- 11 Software Integrations
- Manage
- 143 Governance & Security
- 11 Domo Community Gallery
- 49 Product Releases
- 13 Domo University
- Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 116 Community Announcements
- 5K Archive