HELP! How to replace multiple filters criteria with a single keyword excel list ?

[elstar]

Hi all

I have an existing workflow where I utilized 2 different Filters (by keywords) based on 2 different column headers. The output result is based on the Union of all TRUE value from each filter. 

I'm trying to streamline this workflow: The objective is to have a keyword list in Excel, so that any keywords addition or removal can be done by amending the Excel rather than going into Alteryx to edit the criteria. I saw some solutions provided like Find and Replace but does not seem to work for my case - i could have done it wrongly though. Below is a simplified data for reference:

Any suggestions please? Thanks very much in advance!

Filter criteria:

Gender = Male

Favorite Sports = Soccer OR Basket

ID	Gender	Favorite Sports
1	Male	Badminton; Soccer; Basketball
2	Female	Tennis
3	Female	Basketball
4	Male	Badminton
5	Male	Soccer; Tennis
6	Male	Soccer; Badminton
7	Female	Soccer; Basketball
8	Male	Basketball; Tennis

[Luke_C]

Hi @elstar 

Here's one way:

1. Assuming favorite sports are always separated by a semi colon, split them to rows (and trim leading spaces)
2. Then use a Join tool to do the filtering, first on gender then on sport
3. Summarize the resulting IDs and join back to the original dataset to get the records that meet the criteria. 

[elstar]

Thanks much @Luke_C  ! 

my actual data is not separated by semi column and is in fact in sentences. My apologies i did not put my filter crtieria accurately in my initial post.

Filtering:

1st filter: Gender = Male 

Remaining FALSE value to be filtered

2nd filter: Favorite Sports = Contains Soccer OR Basketball

Remaining FALSE value to be filtered

3rd filter: Availability = Contains Morning or Any

Join the filters (TRUE) as final output. 

Excel data:

ID	Gender	Favorite Sports	Availability
1	Male	My favorite sports is Badminton, Soccer and Basketball	I can play in the afternoon after 3pm
2	Female	I love Tennis	I play in the morning
3	Female	Basketball is my life. I play every single day	I am available the whole day
4	Male	Badminton	I prefer evening but am okay with afternoon too
5	Male	I like Soccer and Tennis but if i have to choose it is soccer	Evening!
6	Male	Soccer is my favorite	any time any where
7	Female	Soccer and Basketball	morning will be nice
8	Male	First is basketball followed by tennis	anytime

[Luke_C · Accepted solution]

Hi @elstar 

A combination of find and replace tools should work. The tools are set to 'append' mode, so if it finds the filter value it will populate a column and list the value that matched. You can then filter for columns that had a matching value in all 3.

Let me know if this works

[elstar]

Thanks so much @Luke_C , it works wonder! My initial workflow was missing the "Filter" you have included. Thanks again~

[gracious]

HI All/ @Luke_C, 

I tried this formula but gave me blank in my output. (in other instance I was able to generate what I need). what could be my problem? 

Thanks, 

gracious

[gracious]

Hi, 

I did not choose "any part of the filed" in the Find within Field, thus the error. Now corrected, I wrongfully indicated "Entire Field". 

Thanks, 

gracious