filtering the data based on each category level with conditions

[Riiiiita]

Hi there, I'm new to Alteryx and I'm trying to filter the column which contains 4 categories, based on which I would like to apply the filter.

I'd appreciate it if someone with more experience could offer advice or point me in the right direction. Thank you!

Under the same destination, if the Transportation is "Any", then keep the record, and delete the rest; if there's no record as "Any", then keep the rest.

Database:

Name	Transportation	Destination
Amy	Any	Home
Amy	Bus	Home
Amy	Car	School
Amy	Bus	School
Sam	Tram	Home
Sam	Light Rail	Home
Sam	Bike	Home
Sam	Any	Home
Chloe	Walk	Home
Chloe	Motorbike	Home
Chloe	Ferry	Home
Chloe	Scooter	Home
Ben	Train	School
Ben	Any	School
Ben	Any	Home
Ben	Uber	Home

Expected Result:

Name	Transportation	Destination
Amy	Any	Home
Amy	Car	School
Amy	Bus	School
Sam	Any	Home
Chloe	Walk	Home
Chloe	Motorbike	Home
Chloe	Ferry	Home
Chloe	Scooter	Home
Ben	Any	School
Ben	Any	Home

[binuacs · Accepted solution]

@Riiiiita a batch macro approach

[Qiu · Accepted solution]

@Riiiiita 
We can filter that record containing "Any" then do a join with original data stream, then union with the Left Anchor data stream.

[cristiane_dsc · Accepted solution]

I used Summarize to group by Name and Destination, and concatenate Transportation. Then I used a Formula to create a new column:

IF Contains([Concat_Transportation], "Any") THEN "Any" ELSE [Concat_Transportation] ENDIF

After that, I split the concatenated Transportation records back into lines (using comma as the separator). The Select tool removed the concatenated column and renamed the extra column created with the Formula tool, and finally the rows were sorted in ascending order by Name.

I hope this helps!

[flying008 · Accepted solution]

Hi, @Riiiiita 

Another way for you :