Help with exclude formula

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.

Answers

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    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.

  • 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
    GrantSmith Indiana 🔴

    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