Beast Mode exclude or include

Rvannoy · 2021-05-07T16:23:32+00:00

There was an error rendering this rich post.

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?

Find more posts tagged with

Accepted answers

Rvannoy

The checkString worked. Thank you

All comments

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

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

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

Hey @Rvannoy ,

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

@DuncanDomo

GrantSmith

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

Rvannoy

The checkString worked. Thank you

jaeW_at_Onyx

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