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
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
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),",")
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
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
I have a similar thing I want to do - but Looker can only do a AND/OR operator with 2 expressions. I dont know if this was an update at some time.
So you can’t do : if(substring(${stage.name},1,2) = “PL” or ${stage.name} = “PreLegal” or ${stage.name} = “Caveat”, “Pre Legal”, “Normal”)
I am putting this in LookML, but it is a pity the AND/OR operator is so limited.