Beast Mode exclude or include

I have the below beast mode.

(CASE when `vessel`like('%RB%') then 'exclude'

 when `vessel`like('%ROLL%') then 'exclude'

 when `vessel`like('%ADV%') then 'exclude'

 when `vessel`like('%REQ%') then 'exclude'

 when `vessel`='' then 'exclude'

else 'include' end)

It is working except it is not excluding blank cells. I thought using: when `vessel`='' then 'exclude' it would exclude the blank cells. When use it to filter include it includes the blank cells. How do I get it to exclude them?

Best Answer

Answers

  • Hi @Rvannoy

    DOMO probably thinks they are null.. so trying using IfNull(), here is some demo data and result:


    Here is the code for checkString BeastMode..

    Case 
      when `Vessel` like '%RB%' then 'Exclude'
      when `Vessel` like '%roll%' then 'Exclude'
      when `Vessel` like '%adv%' then 'Exclude'
      when IFNULL(`Vessel`, 'Y')='Y' then 'Exclude'
      When `Vessel` = ' ' then 'Exclude'
      else 'Include'
     end
    

    I left an empty string test in there, but I suspect you don't need it. I think the IfNull will do what you need.

    Hopefully that works for you.

    Take care,

    DuncanDomo

  • Rvannoy
    Rvannoy ⚪️

    Thank you I tried the suggestion but it is still not excluding the blank cells. It is still showing the blank cells when I filter by include.

    If I use the formula:

    IFNULL(`vessel`,'exclude')

    by itself and it works, but I can't get it to combine with the other logic.

  • Rvannoy
    Rvannoy ⚪️

    Thank you I tried the suggestion but it is still not excluding the blank cells. It is still showing the blank cells when I filter by include.

    If I use the formula:

    IFNULL(`vessel`,'exclude')

    by itself and it works, but I can't get it to combine with the other logic.

  • Hey @Rvannoy ,

    Please copy your full code for CASE with IFNULL and I will try it out..

    @DuncanDomo

  • GrantSmith
    GrantSmith Indiana 🥷

    Hi @Rvannoy

    How are you filtering your data? Are you using the checkString column or the Vessel column? I'd make sure to filter based off the checkString column as it's easier to maintain.

    You may also gain some advantage by wrapping your Vessel column in a TRIM function to remove any leading or trailing whitespace.

    CASE 
      WHEN `Vessel` like '%RB%' THEN 'Exclude'
      WHEN `Vessel` like '%roll%' THEN 'Exclude'
      WHEN `Vessel` like '%adv%' THEN 'Exclude'
      WHEN IFNULL(TRIM(`Vessel`), '') = '' THEN 'Exclude'
    ELSE
     'Include'
    END
    




    **Was this post helpful? Click the heart icon**

    **Did this solve your problem? Accept it as a solution!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤
    edited May 2021

    The easier to read variant.

    CASE 
      WHEN `Vessel` like '%RB%' THEN 'Exclude'
      WHEN `Vessel` like '%roll%' THEN 'Exclude'
      WHEN `Vessel` like '%adv%' THEN 'Exclude'
      WHEN `Vessel` = '' OR `Vessel` is null THEN 'Exclude'
    ELSE
     'Include'
    END
    



    Jae Wilson
    Check out my Domo Training YouTube Channel

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"