Beast Mode exclude or include

Rvannoy
Rvannoy Member
in Magic ETL

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

  • DuncanDomo
    DuncanDomo Contributor

    Hi @Rvannoy

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

    image.png


    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 Member

    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 Member

    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.

  • DuncanDomo
    DuncanDomo Contributor

    Hey @Rvannoy ,

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

    @DuncanDomo

  • GrantSmith
    GrantSmith Coach

    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 Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Rvannoy
    Rvannoy Member
    Answer ✓

    The checkString worked. Thank you

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    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 ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories