Conditional LIKE Statements, Does not Contain, NOT LIKE: Beastmode Only

leeloo_dallas

Hey guys,

To organize my blox_section column I created a beastmode formula under the Data tab in the Analyzer tool to create a new column called "Content Topic":

My Case Statement is the following:

(Case 
 WHEN `blox_section` LIKE '%crime%' THEN 'CRIME & COURTS'
 WHEN `blox_section` LIKE '%sports/high-school%' THEN 'HIGH SCHOOL SPORTS'
 WHEN `blox_section` LIKE '%govt-and-polit%' THEN 'GOVERNMENT/POLITICS'
 WHEN `blox_section` LIKE '%/sports%' THEN 'OTHER SPORTS (Bills, STL, Sub required)'
 WHEN `blox_section` LIKE '%/sports/college%' OR `blox_section` LIKE '%/sports/husker%' OR `blox_section` LIKE '%/sports/wildcat%' THEN 'COLLEGIATE SPORTS (Standard + Husker-labeled section)'
 WHEN `blox_section` LIKE '%business%' THEN 'BUSINESS'
 WHEN `blox_section` LIKE '%lifestyles%' THEN 'LIFESTYLE'
 when `blox_section` like '%announcement%' OR `blox_section` LIKE '%obit%' THEN 'OBITUARIES AND ANNOUNCEMENT'
 WHEN `blox_section` LIKE '%opinion%' THEN 'OPINION/LETTERS TO THE EDITOR'
 WHEN `blox_section` LIKE '%weather%' THEN 'WEATHER'
 WHEN `blox_section` LIKE '%news%' AND `blox_section` LIKE '%national%' OR `blox_section` LIKE '%world%' THEN 'NATIONAL/INTL NEWS'
 WHEN `blox_section` LIKE '%education%' THEN 'EDUCATION'
 WHEN `blox_section` LIKE '%entertainment%' THEN 'ENTERTAINMENT/THINGS TO DO'
 WHEN `blox_section` LIKE '%health%' THEN 'HEALTH'
 WHEN `blox_section` LIKE '%eedition%' THEN 'EEDITION'
 WHEN `blox_section` IS NULL OR `blox_section`='' THEN 'EMPTY'
ELSE 'OTHER' END)

My problem arises when two sections have the same string I am trying to categorize.

Example: blox_section = "lifestyles, health-and-fit"

Because lifestyles appears first in this string, Content Topic is categorized as "LIFESTYLES". The Content Topic it should be is 'HEALTH' as health exists in the string.

I want to create conditional like statements to exclude instances like this, but how do I do this when you can't use NOT LIKE in beastmode?

DISCLAIMER: This dataset is very very large, therefore performing this in Beastmode is not only more ideal, but it's easier to perform validations and preview whether or not my case statement is working as intended as this is only to create 1 card.

GrantSmith

Hi @leeloo_dallas

I may be misunderstanding your request but You can use NOT LIKE in a case statement

WHEN `blox_section` LIKE '%lifestyles%' THEN 'LIFESTYLE' AND `blox_section` NOT LIKE '%health%' THEN 'LIFESTYLE'

A simpler way would be to put your WHEN clauses in the order you wish for them to evaluate. So if any string contains health and you want it to have HEALTH as the value even though it may have lifestyles you'd put your HEALTH check before the LIFESTYLES check as case statements are evaluated on a first come, first served basis.

(Case 
 WHEN `blox_section` LIKE '%health%' THEN 'HEALTH'
 ...
 WHEN `blox_section` LIKE '%lifestyles%' THEN 'LIFESTYLE'
...
ELSE 'OTHER' END)

If you're wanting to exclude the multiple selection values and they will have a comma in them (and only if it's in a multi-selection) you could add a case to your statement to set a new value to then filter out:

(Case 
 WHEN `blox_section` LIKE '%,%' THEN 'MULTI-SELECT'
 WHEN `blox_section` LIKE '%crime%' THEN 'CRIME & COURTS'
 WHEN `blox_section` LIKE '%sports/high-school%' THEN 'HIGH SCHOOL SPORTS'
 WHEN `blox_section` LIKE '%govt-and-polit%' THEN 'GOVERNMENT/POLITICS'
 WHEN `blox_section` LIKE '%/sports%' THEN 'OTHER SPORTS (Bills, STL, Sub required)'
 WHEN `blox_section` LIKE '%/sports/college%' OR `blox_section` LIKE '%/sports/husker%' OR `blox_section` LIKE '%/sports/wildcat%' THEN 'COLLEGIATE SPORTS (Standard + Husker-labeled section)'
 WHEN `blox_section` LIKE '%business%' THEN 'BUSINESS'
 WHEN `blox_section` LIKE '%lifestyles%' THEN 'LIFESTYLE'
 when `blox_section` like '%announcement%' OR `blox_section` LIKE '%obit%' THEN 'OBITUARIES AND ANNOUNCEMENT'
 WHEN `blox_section` LIKE '%opinion%' THEN 'OPINION/LETTERS TO THE EDITOR'
 WHEN `blox_section` LIKE '%weather%' THEN 'WEATHER'
 WHEN `blox_section` LIKE '%news%' AND `blox_section` LIKE '%national%' OR `blox_section` LIKE '%world%' THEN 'NATIONAL/INTL NEWS'
 WHEN `blox_section` LIKE '%education%' THEN 'EDUCATION'
 WHEN `blox_section` LIKE '%entertainment%' THEN 'ENTERTAINMENT/THINGS TO DO'
 WHEN `blox_section` LIKE '%health%' THEN 'HEALTH'
 WHEN `blox_section` LIKE '%eedition%' THEN 'EEDITION'
 WHEN `blox_section` IS NULL OR `blox_section`='' THEN 'EMPTY'
ELSE 'OTHER' END)

GrantSmith

Hi @leeloo_dallas

I may be misunderstanding your request but You can use NOT LIKE in a case statement

WHEN `blox_section` LIKE '%lifestyles%' THEN 'LIFESTYLE' AND `blox_section` NOT LIKE '%health%' THEN 'LIFESTYLE'

A simpler way would be to put your WHEN clauses in the order you wish for them to evaluate. So if any string contains health and you want it to have HEALTH as the value even though it may have lifestyles you'd put your HEALTH check before the LIFESTYLES check as case statements are evaluated on a first come, first served basis.

(Case 
 WHEN `blox_section` LIKE '%health%' THEN 'HEALTH'
 ...
 WHEN `blox_section` LIKE '%lifestyles%' THEN 'LIFESTYLE'
...
ELSE 'OTHER' END)

If you're wanting to exclude the multiple selection values and they will have a comma in them (and only if it's in a multi-selection) you could add a case to your statement to set a new value to then filter out:

(Case 
 WHEN `blox_section` LIKE '%,%' THEN 'MULTI-SELECT'
 WHEN `blox_section` LIKE '%crime%' THEN 'CRIME & COURTS'
 WHEN `blox_section` LIKE '%sports/high-school%' THEN 'HIGH SCHOOL SPORTS'
 WHEN `blox_section` LIKE '%govt-and-polit%' THEN 'GOVERNMENT/POLITICS'
 WHEN `blox_section` LIKE '%/sports%' THEN 'OTHER SPORTS (Bills, STL, Sub required)'
 WHEN `blox_section` LIKE '%/sports/college%' OR `blox_section` LIKE '%/sports/husker%' OR `blox_section` LIKE '%/sports/wildcat%' THEN 'COLLEGIATE SPORTS (Standard + Husker-labeled section)'
 WHEN `blox_section` LIKE '%business%' THEN 'BUSINESS'
 WHEN `blox_section` LIKE '%lifestyles%' THEN 'LIFESTYLE'
 when `blox_section` like '%announcement%' OR `blox_section` LIKE '%obit%' THEN 'OBITUARIES AND ANNOUNCEMENT'
 WHEN `blox_section` LIKE '%opinion%' THEN 'OPINION/LETTERS TO THE EDITOR'
 WHEN `blox_section` LIKE '%weather%' THEN 'WEATHER'
 WHEN `blox_section` LIKE '%news%' AND `blox_section` LIKE '%national%' OR `blox_section` LIKE '%world%' THEN 'NATIONAL/INTL NEWS'
 WHEN `blox_section` LIKE '%education%' THEN 'EDUCATION'
 WHEN `blox_section` LIKE '%entertainment%' THEN 'ENTERTAINMENT/THINGS TO DO'
 WHEN `blox_section` LIKE '%health%' THEN 'HEALTH'
 WHEN `blox_section` LIKE '%eedition%' THEN 'EEDITION'
 WHEN `blox_section` IS NULL OR `blox_section`='' THEN 'EMPTY'
ELSE 'OTHER' END)

leeloo_dallas

I realized that NOT LIKE is problematic in views and was able to get the result I wanted. Unfortunately rearranging the WHEN clause wouldn't work because there's cases where the situation is reversed too.