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

[neoiris]

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

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

[geraldo · Accepted solution]

@neoiris 

an example of how to do

[neoiris]

Hi

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 · Accepted solution]

Hi, @neoiris 

FYI.

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

[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,