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

leeloo_dallas
leeloo_dallas Member
in Charting

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":

image.png


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.

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • leeloo_dallas
    leeloo_dallas Member

    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.

Categories