Bucketing different text fields if there is a match

agolla2

I'm trying to count different text fields from a column called Reason and there are different reasons in that column. One row might have single or multiple reasons separated by a comma. Below are examples.

1) Reason 1
2) Reason 1, Reason 2, Reason 3
3) Reason 3, Reason 2

Beast mode using currently:

CASE
WHEN Reason LIKE '%Reason 1%' THEN 'Reason 1'
WHEN Reason LIKE '%Reason 2%' THEN 'Reason 2'
WHEN Reason LIKE '%Reason 3%' THEN 'Reason 3'
ELSE 'Not Applicable'
END

This beast mode currently not counting the multiple reasons because it is just looking for a match and if there is match with one reason it is jumping to the next filed.

Current output:

Reason 1 - Count 2
Reason 2 - Count 0
Reason 3 - Count 1

Expected Output:

Reason 1 - Count 2

Reason 2 - Count 2

Reason 3 - Count 2

MarkSnodgrass

An easy way to get accurate counts of this is to break up your lists into individual items, with each item in its own row. You can do this dynamically in Magic ETL using this process:

https://youtu.be/kxjJ0T4Q4OE