HELP with Case When Statement
I need help on how to code this in beast mode.
(It's like I need to tag a specific column that it's either included/excluded based on the CASE WHEN statement). I just dont know how to do in using the proper syntax.
CASE WHEN ...........
Then <field name> 'excluded'
Else <field name> 'included'
END
Answers
-
Hi, Stuck.
Thank you so much for looking into this.
Here's what I needed to do specifically, showing the 3 columns and data below (separating them with comma)
<alarm ID> <alarm description> <alarm action>
11111, Lost Connectivity, Pending
11111, Lost Connectivity, Pending
11111, Lost Connectivity, Disregard
11112, Lost Connectivity, Pending
11112, Lost Connectivity, Contacted
11113, Case Temp, Pending
11113, Case Temp, Disregard
11114, Lost Connectivity, Contacted
11115, Lost Connectivity, Disregard
11116, Lost Connectivity, Pending
11116, Lost Connectivity, Disregard
I have to exclude all Lost Connectivity alarms with Disregard action (including their pending actions).
So in this case, I have to exclude the following:
alarm IDs - 11111, 11115, 11116
Not sure how to code this in beast mode
case when
`Alarm Description` = 'Lost Connectivity' AND `alarm action` = 'Disregard'
then `alarm ID` = exclude (can't do this one since this will only remove the one with disregard action, not all historical data )
else `alarm ID` = include
END
0 -
@Zel you're close. You have the when correct, you just need to your then and else to be like's Stuck's. Like this:
case when `Alarm Description` = 'Lost Connectivity' AND `alarm action` = 'Disregard' then 'Exclude' ELSE 'Include' END
Drag this beast mode into your filters and filter to include or exclude, depending on what you want to do.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
Hi, @Stuck @MarkSnodgrass.
Apparently, something is still lacking in the code, because when I do the code listed below, it's just removing the specific alarm ID with the 'disregard' action, not the alarm ID containing disregard+previous actions.
- case when
- `Alarm Description` = 'Lost Connectivity' AND `alarm action` = 'Disregard'
- then 'Exclude'
- ELSE 'Include'
- END
0 -
Are you saying that if an alarm id has a row that has Lost Connectivity and Disregard, you not only want to exclude that row, but also all rows with that same alarm id? If so, you won't be able to do this in a beast mode. You will need to do this in Magic ETL. You would filter to the records you want to exclude and then do a left join back to your original dataset and then filter out the appropriate records.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
@MarkSnodgrass yes, that's exactly how I wanted to do it.
Oh, so this has to be done via ETL. Do you have any idea how it should look like if it's in Magic ETL? Haven't exactly used it yet, but will try do some research, too.
0 -
Hi @Zel
I used a filter tile to identify all alarm IDs that have both "Lost Connectivity" and "Disregard". I then left join that back to your original dataset after adding a column that says to exclude those IDs.
The resulting dataset looks like this:
Here is the JSON code that you can copy and paste into an ETL to mimic what I did.
{"contentType":"domo/dataflow-actions","data":[{"name":"@Zel Dojo","id":"6d9d3106-a084-481d-a69d-74840f17d018","type":"LoadFromVault","gui":{"x":36,"y":180,"color":3238043},"dependsOn":[],"removeByDefault":false,"notes":[],"dataSourceId":"864c1409-461e-46fc-b4b3-0ed644903782","executeFlowWhenUpdated":true,"onlyLoadNewVersions":false,"columnSettings":{},"visiblePartitionColumn":null,"versionWindow":null},{"name":"LostConnectivity and Disregard","id":"49f0cb44-56bb-4e19-ae8b-b83be193e3c6","type":"Filter","gui":{"x":144,"y":288},"dependsOn":["6d9d3106-a084-481d-a69d-74840f17d018"],"removeByDefault":false,"notes":[],"filterList":[{"leftField":"alarmDescription","rightValue":{"value":"Lost Connectivity","type":"STRING"},"operator":"EQ","andFilterList":[{"leftField":"alarmAction","rightValue":{"value":"Disregard","type":"STRING"},"operator":"EQ","andFilterList":[]}]}]},{"name":"Group By","id":"b8d8da16-2db0-4d88-9287-b349901d7d54","type":"GroupBy","gui":{"x":264,"y":288},"dependsOn":["49f0cb44-56bb-4e19-ae8b-b83be193e3c6"],"removeByDefault":false,"notes":[],"addLineNumber":false,"lineNumberFieldname":null,"giveBackRow":false,"allRows":false,"groups":[{"name":"alarmID"}],"partitionedAggregation":false,"fields":[{"name":"Count","source":"alarmID","type":"COUNT_ALL"}]},{"name":"Add Constants","id":"2c3aa4bb-07f5-45f4-abfa-26e9747f1fa5","type":"Constant","gui":{"x":372,"y":288},"dependsOn":["b8d8da16-2db0-4d88-9287-b349901d7d54"],"removeByDefault":false,"notes":[],"fields":[{"name":"FilterCriteria","type":"STRING","value":"exclude"}]},{"name":"Join Data","id":"e9e76a06-7a82-429a-bb3f-f1e912e1d6bc","type":"MergeJoin","gui":{"x":492,"y":180},"dependsOn":["6d9d3106-a084-481d-a69d-74840f17d018","2c3aa4bb-07f5-45f4-abfa-26e9747f1fa5"],"removeByDefault":false,"notes":[],"joinType":"LEFT OUTER","step1":"6d9d3106-a084-481d-a69d-74840f17d018","step2":"2c3aa4bb-07f5-45f4-abfa-26e9747f1fa5","keys1":["alarmID"],"keys2":["alarmID"],"schemaModification1":[],"schemaModification2":[{"name":"alarmID","rename":"","remove":true},{"name":"Count","rename":"","remove":true}],"partitioningInputId":""},{"name":"Add Formula","id":"9a80fd0d-33fb-4085-a20a-3ab856f5a762","type":"ExpressionEvaluator","gui":{"x":600,"y":180},"dependsOn":["e9e76a06-7a82-429a-bb3f-f1e912e1d6bc"],"removeByDefault":false,"notes":[],"expressions":[{"fieldName":"FilterCriteria","expression":"IFNULL(`FilterCriteria`,'include')","settings":null}]},{"name":"@Zel output","id":"f32d6203-2ab9-42b0-b44b-f0ee823202f3","type":"PublishToVault","gui":{"x":708,"y":180},"dependsOn":["9a80fd0d-33fb-4085-a20a-3ab856f5a762"],"removeByDefault":false,"notes":[],"dataSource":{"name":"@Zel output"},"partitionIdColumns":[],"upsertColumns":[]}]}
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman2 -
Hi, @ST_-Superman-_ wow, this is awesome! Thank you so much for this. Will try this in ETL and let you know the feedback asap :D
1 -
Thanks for picking this up @MarkSnodgrass & @ST_-Superman-_ - great solution! :)
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 737 Beast Mode
- 56 App Studio
- 40 Variables
- 684 Automate
- 176 Apps
- 452 APIs & Domo Developer
- 46 Workflows
- 10 DomoAI
- 35 Predict
- 14 Jupyter Workspaces
- 21 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 123 Manage
- 120 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive