How do I select records based on a separate list of acceptable values?

[abragg0103]

I have a large file of data and one of the fields is [Status]. It's a freeform field that is populated upstream, but there are a few systematic status that I want to look at. I have a separate input file that is just a list of my desired statuses. In SQL I would just do "SELECT * FROM t1 WHERE t1.[Status] IN (SELECT t2.[Status] FROM t2)"

I can't find any tool similar in Alteryx that let's me use a recordset to extra items from another recordset. I tried the filter tool but I don't think it has what I need.

[AngelosPachis · Accepted solution]

Hi @abragg0103 ,

Assuming that your data table looks like this

and your list of acceptable values is A and D, you can use a find & replace tool to find those acceptable values in the Status column and append a Keep flag

Then you can use a filter tool, to exclude all nulls values, which will leave you only the status in your separate list.

For this example, a join tool would also work, but the find and replace tool allows you to look for values at any part of the string and match case insensitive finds.

Hope that helps,

Angelos

[abragg0103]

Thank you. Both solutions will work great.