Beast Mode exclude or include

Rvannoy
Rvannoy ⚪️

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

  • Rvannoy
    Rvannoy ⚪️
    Accepted Answer

    The checkString worked. Thank you

Answers

  • DuncanDomo
    DuncanDomo ⚪️

    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
    


  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🔵
    edited May 27

    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
    


Sign In or Register to comment.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!