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