Filtering against a list of values

[tbrown06]

I am trying to filter data based on a list of zip codes. My data contains a field called locations. Those cells contain a list of zip codes in which that product is carried (some cells are hundreds of zip codes long separated by " "+"|"+" "). If I want to filter out all the products that are in a certain county by filtering against another list of zip codes, how would I go about doing that?

In Excel we use this formula, =SUMPRODUCT(--ISNUMBER(SEARCH([named range of zips we want to filter], H2)))>0

H2 is the cell in the location field that contains the list of zips. The formula above returns a TRUE or FALSE value. Then we filter down to all the true values and copy and paste them out. Any help on how to do this using the filter tool in Alteryx would be a huge help!

[Storm · Accepted solution]

I am still very new here; thus hesitant to offer a partial solution; but here goes.

I'd use the formula tool on "locations" field, to replace ' ' with nothing. This results in each cell containing X number of zips, pipe delimited.

I'd then use the parse tool on the locations field; enter | as delimiter and choose split to rows. This results in a vertical list of the product, each individual location (zip) and whatever other fields you selected in.

I'd then just join your list of excluded zip codes to this stream, using the join tool. Any products which emerge from the direct (center) join should be EXCLUDED; so I'd join these results as a right input back to my original data as a left input, and take only the left output of this join (i.e. all rows where products didn't match these results).

I'm sure a more elegant solution exists...as I said I am but a newbie :)

[tbrown06]

Any ideas on how to return the data back to its original format after I've pulled out the unwanted zips? (ex. 60601 | 60602 | 60603...) Or is there a simpler method to this process anyone is aware of? The solution offered kind of worked but it grew the file from about 50,000 records to over 12,000,000.

[JohnJPS · Accepted solution]

Not sure what your data looks like, but if it's split to one row per zip, also with a product column, you could sum grouped on product, and instead of doing a numeric sum, do a string concatenation on the zip field, separated by the pipe character. If the data is being treated as a number, first use a "select" tool to cast it to a string type.

[Storm]

Thanks John, I was also going to respond something like this.  Probably want to do a group by product after the zip exclusion, prior to joining back to the original data, to avoid the duplication.

[tbrown06]

Thanks! You guys are life savers I never would've figured all this out. It's a messy solution but it works the way I need it to.