Help with exclude formula

Rvannoy
Rvannoy Member
in Magic ETL

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 Coach

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • DuncanDomo
    DuncanDomo Contributor

    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 Coach

    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


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories