HELP with Case When Statement

Zel · 2023-02-15T21:50:05+00:00

There was an error rendering this rich post.

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

Find more posts tagged with

Accepted answers

All comments

Samuel.

Hi @Zel

CASE
WHEN `column x` = 'y' THEN 'Excluded'
ELSE 'Included'
END

Then use the filter process to include or exclude whatever values from column x you want 😊

Hopefully, this is helpful.

Stuck

Zel

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)

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

MarkSnodgrass

@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.

Zel

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

MarkSnodgrass

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.

Zel

@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.

ST_Superman

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":[]}]}

Zel

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

Samuel.

Thanks for picking this up @MarkSnodgrass & @ST_-Superman-_ - great solution! :)