Help with exclude formula

Rvannoy · 2021-05-10T02:58:43+00:00

There was an error rendering this rich post.

Rvannoy

Used this formula

(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)

thinking using:

when 'vessel'='' then 'exclude'

would work to exclude the blank cells as well as the others. it did not.

Was given suggestion to use:

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 IFNULL(`Vessel`,'Y')= 'Y' then 'Exclude'

when `vessel`='' then 'exclude'

else 'Include'

end

This also did not work to exclude the blank cells. If I use

IFNULL(`vessel`,'exclude')

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

Any help would be greatly appriciated.

Find more posts tagged with

Accepted answers

All comments

MarkSnodgrass

You can add this as another WHEN statement:

WHEN 'vessel' IS NULL THEN 'exclude'

Also for your when `vessel`='' then 'exclude' statement, try changing it to:

when TRIM(`vessel`) ='' then 'exclude'

This will remove any spaces that are in the field.

DuncanDomo

Hey @Ritwik

I think @MarkSnodgrass is probably right, some how maybe you have nulls and empty strings in there..

here's Mark's idea in one case statement...

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 trim(`Vessel`) = '' then 'Exclude'
 	else 'Include'
 end

try that out..

GrantSmith

One thing to be careful of though with IFNULL is that if Vessel happened to have a value of Y It would also be excluded. I'd recommend using @MarkSnodgrass 's version using an explicit IS NULL check or alternatively combining your '' check and the NULL check together since you do have an explicit string you're wanting to exclude:

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