Using IN("X","Y","Z") logic in a worksheet formula

I need to perform a calculation using a formula that checks a data value against a short list of possible strings. In SQL I would use

IF DATA_COLUMN IN ("X","Y","Z") THEN

However, in the ThoughtSpot formulas interface I don't see the "IN" logic -- and I can't find a ready alternative. I do not want to put my list in a separate data table.

How can I use a formula to check against a list of strings like this?

Thanks!

8replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Joe Bloom You should be able to use 'Or' logic instead of 'In'.  For example; If (Data_Column = 'X' or Data_column = 'Y' or Data_column = 'Z') THEN ....

    More typing than with 'In' but it should do the trick.

    Like
  • Thanks Micah I'm recoding a column with about 30 possible values into one with 3, so with OR I will be looking at a pretty unwieldy formula. That's the best work around?

    Like
  • Joe Bloom I believe using 'OR' is probably the best way.  If i have a long formula like this, i'll often use a text editor (Text Wrangler or Notepad++) to make the process of creating / editing fast.  Once it's created in the text editor, you can paste it into the ThoughtSpot formula editor.

    Like
  • Micah Terry Yeah, I would definitely do it in Notepad++. Thanks for the quick feedback.

    Like
  • Joe Bloom no problem.  Looks like there are a few product requests in the Feedback section of the community.  You might consider throwing a request out there for an 'in' operator for formulas.  This would a be a nice feature for certain types of formulas.

    Like 1
  • Micah Terry Great idea. Will do!

    Like 1
  • Joe Bloom - I have also used a boolean formula field like "isinlist" in a worksheet when I know that list filter is going to be used often - something like: isinlist = substr ( opportunity , 0 , 1 ) = 'a' or substr ( opportunity , 0 , 1 ) = 'b' or substr ( opportunity , 0 , 1 ) = 'c' or substr ( opportunity , 0 , 1 ) = 'd' which would list all the opportunities that started with a, b, c or d - then, on my search bar or in a filter, I simply add in the field "isinlist" - doesn't work for all situations, but can be very useful.

    Like 1
  • Dave Cohen That's not going to be a good fit for my current use case, but thanks for the suggestion!

    Like
Like Follow
  • Status Answered
  • 2 yrs agoLast active
  • 8Replies
  • 659Views
  • 3 Following