Case statement with multiple field conditions
Hello,
I'm trying to write a beast mode fiter that looks for a condition in one field and also searches multiple fields to return a "yes" value. Seems simple enough, but...
I want it to:
Look in CPT code field and find all records with CPT code of 20610, then look in 5 diagnosis code fields to exclude the procedures that deal with Osteoarthritis. Something like:
case when `cpt_code` = '20610' AND
`icd_1` <> '71516'
`icd_2` <> '71516'
`icd_3` <> '71516'
`icd_5` <> '71516'
then 'Yes'
else "No'
end
Basically, this would show me all the large joint injections EXCEPT the ones that deal with OsteoArthritis of a joint.
I keep getting syntax errors... any thoughts? The code for OA could exist in any one of the ICD fields, which is why it needs to search all 4 fields for the OA code (test for that condition) before it allows this procedure to show up on the graph. Need help "structuring" the beast mode to accomplish this. Thanks in advance... I don't see any examples of this type of filtering in the Dojo.
Thanks!
Comments
-
Hi,
Give this a try:
CASE WHEN `cpt_code` = '20610' AND
`icd_1` != '71516' AND
`icd_2` != '71516' AND
`icd_3` != '71516' AND
`icd_4` != '71516'
THEN
'Yes'
ELSE
'No'
ENDBasically, you were missing the AND operators and had a double cote in :
«else "No'». The != (not equal) operator is a little faster when comparing text fields but you could still use the <> operator.
Hope this helps.
Ricardo Granada
**If the post solves your problem, mark it by clicking on "Accept as Solution"
**You can say "Thank you" by clicking the thumbs up in the post that helped you.0
Categories
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 468 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 193 Visualize
- 252 Beast Mode
- 2.1K Charting
- 11 Variables
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 173 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive