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
- Data Connections
- 1.3K Connectors
- 309 Workbench
- 17 Cloud Integrations
- Data & ETL
- 2.3K Magic ETL
- 120 SQL DataFlows
- 666 Datasets
- 827 Beast Mode
- Visualize & Apps
- 90 App Studio
- 198 Pro-code Components
- 2.6K Charting & Analyzer
- 46 Calculations & Variables
- AI & Data science
- 23 Domo AI & AI Chat
- 4 Managing AI
- 18 Jupyter Workspaces
- 122 Workflows
- Distribute
- 118 Domo Everywhere
- 284 Reporting
- Manage
- 145 Governance & Security
- 489 APIs
- 11 Add-ins & Plugins
- 13 Domo Community Gallery
- 49 Product Releases
- 13 Domo University
- Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 117 Community Announcements
- Automate
- 5K Archive