Hi I'm beginner in Alteryx and would like to seek expert's help. In following table, if ID has Category = 'Passport', check if the same ID has any of following category ' Work permit' or 'Employment Visa' or 'Permanent Resident' or Null. If yes, then the Passport row needs to be removed. Can you please help how to set this up in Alteryx? Input IDCategoryGenderSalary61053Work permitM500061053Employment VisaM500061053Permanent ResidentM500061053PassportM50003443PassportF30003443 F30002222 M1500 Output IDCategoryGenderSalary61053Work permitM500061053Employment VisaM500061053Permanent ResidentM50003443 F30002222 M1500

Rows under same ID, how to remove specific row by checking other value in same column

I'm beginner in Alteryx and would like to seek expert's help.

In following table, if ID has Category = 'Passport', check if the same ID has any of following category ' Work permit' or 'Employment Visa' or 'Permanent Resident' or Null.

If yes, then the Passport row needs to be removed.

Can you please help how to set this up in Alteryx?

Input

ID	Category	Gender	Salary
61053	Work permit	M	5000
61053	Employment Visa	M	5000
61053	Permanent Resident	M	5000
61053	Passport	M	5000
3443	Passport	F	3000
3443		F	3000
2222		M	1500

Output

ID	Category	Gender	Salary
61053	Work permit	M	5000
61053	Employment Visa	M	5000
61053	Permanent Resident	M	5000
3443		F	3000
2222		M	1500

Expression

Data Investigation

Accepted answers

geraldo

@neoiris

an example of how to do

RowsameID.yxmd

flying008

Hi, @neoiris

FYI.

IIF([Category] = 'Passport' && REGEX_Match([Concat_Category], '^.*?(Work permit|Employment Visa|Permanent Resident|\s).*$'), 'x', [Category])

录制_2024_03_07_13_46_43_571.gif

All comments

geraldo

@neoiris

an example of how to do

RowsameID.yxmd

neoiris

Thank you for your great help. The original data has many other columns and I added just two which are Gender and Salary for example.

Is there anyway to keep these columns after the processing is done?

flying008

Hi, @neoiris

FYI.

IIF([Category] = 'Passport' && REGEX_Match([Concat_Category], '^.*?(Work permit|Employment Visa|Permanent Resident|\s).*$'), 'x', [Category])

录制_2024_03_07_13_46_43_571.gif

neoiris

HI @flying008

Thank you for your help. It works!

flying008

Hi, @neoiris

If can help you get your want, please mark it as a solution and give a like for more share.

Manoj_k

HI, @neoiris i found a way to do this without the need of regex,

RowsameID.yxmd

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335