HELP with Case When Statement

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

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.

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

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