support with "in" function

Question

hi all

I am trying to use the "in" function to search for a string in a list of strings (e.g.: search for 'a' in ('a','b','c')) and it works if I hard code the values, but does not work if the values are stored in a variable (column).

Let's say the data looks like this:

[Search]  |  [List]

'a'            |  'a','b','c'

If I configure the formula as follows --> [Search] in ('a','b','c') --> it works, but if I do --> [Search] in [List] --> it does not work (tried with and without the " ' "). Why is that?

p.s.: I know I could use contains([List],[Search]), but I am interested in why the 'in' function does not work...

Thank you.

JoeS · Accepted Answer

Alteryx will be treating the [List] field as one value.

The expression will treat each entry separated by a comma as a whole value, as opposed to the values within those entries. It's not dynamically looking into the values for a comma separate list as well (god that's horrible to try and write, but hopefully makes sense?).

As you say Contains() will work in your scenario, or please have a look at the dynamic formula as part of the CReW macros here