filtering the data based on each category level with conditions

Question

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:

NameTransportationDestinationAmy

AnyHomeAmyBusHomeAmyCarSchoolAmyBusSchoolSamTramHomeSamLight RailHomeSamBikeHomeSamAnyHomeChloeWalkHomeChloeMotorbikeHomeChloeFerryHomeChloeScooterHomeBenTrainSchoolBenAnySchoolBenAnyHomeBenUberHome

Expected Result:

NameTransportationDestinationAmy

AnyHomeAmyCarSchoolAmyBusSchoolSamAnyHomeChloeWalkHomeChloeMotorbikeHomeChloeFerryHomeChloeScooterHomeBenAnySchoolBenAnyHome

binu_acs · Accepted Answer

@Riiiiita a batch macro approach

Qiu · Accepted Answer

@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 Answer

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 Answer

Hi, @Riiiiita

Another way for you :