IF function combined with List function

done
low_priority
watch

(Wayne Henry) #1

Hi there

How do I write the equivalent of “if tutor ID appears in list, yes, otherwise no”?

Sort of like if(tutor.id=list(5237, 8296, 4251),yes,no) but something that actually works! In other words, if the tutor id is any one of those three numbers, yes, otherwise no


The Podium — February 27th, 2019
(Andrew Powell) #2

Hey,

I don’t believe there is an IN comparison operator we can use here unfortunately (+1 to that feature request if it already exists.

You can achieve what you want by using an OR operator though:

if(tutor.id=5237 OR tutor.id=8296 OR tutor.id=4251,yes,no)

Should do the trick.

Here’s the syntax reference for table calculations: https://docs.looker.com/exploring-data/creating-looker-expressions/looker-functions-and-operators#operators_for_any_looker_expression_2

Andy


(Ian) #3

Hi, I would use the contains, substring or position functions to see if your value appears in the list, comparing one by one using OR is ok if you only have say…3 values. To be accurate you will want to compare your value with a , at the start and end to make sure its not a substring of a larger string (or number in this case) inside the list. So make sure you append a , at the start and end of your list as well:
concat(",",list(123,456,789),",")


(Izzy) #4

There’s also a lesser-known function called matches_filter, which I believe is currently only possible in custom fields + custom filters (ie: not table calculations), but it basically lets you use the same filter expressions you’d use in a “matches advanced” type filter—

matches_filter(${view_name.field_name}, `string1,string2,string3,...`)

If you are in a table calc, then those above two options are also good. @IanT is spot on with the idea to use position, for example like:

position(concat(list("123", "456", "789"), ","), ${view.field_name})>0

(Wayne Henry) #6

izzy, thank you so much - two other great methods from Andy and Ian too, but ultimately I went with yours for it’s simplicity, and also I need to be able to use it in custom dimensions. Brilliant! Thanks to Andy and Ian too