Dynamic, comma-separated 'IN' list not filtering as expected

[DataNath]

Hi everyone, a bit of a niche one that I ran into this morning and have been trying to troubleshoot/play around with it - have also tried to find some workarounds with @OllieClarke but thought I'd bring it here as well.

I'm fairly sure I've done this before and it worked but willing to be proven completely wrong. I'm also fully aware that I can just swap this to Contains(<ConcatList>,<TargetField>) but I'm more so just curious about this option not working.

Issue: I have a lookup table of dates (stored as strings in mm/dd/yyyy format) and I'm concatenating these, with quotes at the start and end, separated by "," in order to get a list like "date1","date2","date3". Following this, I am appending this to a main data table, which contains a date field (again in mm/dd/yyyy format). That all ends up looking like this:

Now, I originally expected the following Filter approach to work:

However, as you can see, I'm not getting any True results and there are definitely applicable dates in the data set. Interestingly, if I just copy and paste the actual list into the Filter expression, this does work:

Initial things we've tried:

1) When making the list in the Summarize tool, not adding the 'Start' and 'End' in case Alteryx is automatically wrapping in these itself

2) Both single and double quotes

3) Using XML escape characters instead of double and single quotes i.e. &quot; and &apos;

Thoughts:

1) Is Alteryx somehow containing this i.e. turning what we see as a list into a literal single string? When adding a 'date' that perfectly matches the concatenated list, this passes through as True

2) Can we use another approach to escaping that may allow this to be treated as a list?

[cjaneczko]

Have you Tried Contains?

Contains([Concat_FilterDates],[Inspection Date])

 

[DataNath]

---

@DataNath wrote:

I'm also fully aware that I can just swap this to Contains(<ConcatList>,<TargetField>) but I'm more so just curious about this option not working.

---

Hi @cjaneczko, yes I've tried/am using this.

[Luke_C]

Hi @DataNath 

This is super weird. If you turn it into a macro it works though.

[ChrisTX · Accepted solution]

Check out this post: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/support-with-quot-in-quot-function/td-...

• The expression will treat each entry separated by a comma as a whole value
• As you say Contains() will work in your scenario, or please have a look at the dynamic formula as part of the CReW macros

Chris

[DataNath]

Interesting that you can inject the list like that @Luke_C - looks like sticking to Contains() for now is the easiest route though!

Cheers @ChrisTX - hadn't spotted that but guess that answers my Q and confirms I definitely haven't done this before!

[apathetichell]

I guess I just always do this in a batch macro - or feeding it into dynamic input in-db.

[OllieClarke]

@DataNath Dynamic Replace baby!