I have 2 columns, primaryAssemblies and secondaryAssemblies.
A person could have "Clinical Care" as their primary assembly.
They are allowed at least 1 secondary assembly. So it may look like "Critical Care, Pediatrics"
I need to count each occurrence like so:
(CASE
when `PrimaryAssembly` like '%Allergy Immunology & Inflammation%' or `SecondaryAssemblies` like '%Allergy Immunology & Inflammation%' then 1
when `PrimaryAssembly` like '%Behavioral Science and Health Services Research%' or `SecondaryAssemblies` like '%Behavioral Science and Health Services Research%' then 2
when `PrimaryAssembly` like '%Clinical Problems%' or `SecondaryAssemblies` like '%Clinical Problems%' then 3
end
)
The values showing in the beast mode field are not correct.
I'm trying to count each occurence, whether in the first column (primaryAssemblies) or second column (secondaryAssemblies).
Any ideas?