nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Data Challenge

Community

Alteryx Practice

Accepted answers

Luke_C

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

New Workflow5.yxmd

All comments

Luke_C

Hi @elstar

Here's one way:

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

New Workflow5.yxmd

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

Hi @elstar

Let me know if this works

New Workflow5.yxmd

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15898

binu_acs 15783

MarqueeCrew 13709

apathetichell 13703