How to Filter records if it includes certain value in other filed

[Apple_Orange]

Hi Community,

Can Alteryx able to exclude certain Type as long as one row contains "M". Below is an example:

Type	Vendor	Label
Apple	A	M
Orange	B	M
Apple	C	N
Pear	D	N

Desired result would be

Type	Vendor	Label
Pear	D	N

Thanks

[binu_acs · Accepted solution]

@Apple_Orange one way of doing this 

1. Add a filter tool with the condition Label = “M”

2. Add a join tool - connect from filter tool T anchor and the other connection from input text tool, join the field “Type”

3. run the workflow and check the R - anchor of the join tool

[Apple_Orange]

Hi Binus,

Thanks for looking at it. but the tricky part of this if as long as Apple has one row with M, the other row of Apple needs to be filter out as well. Do you have solution for that. Thanks

[binu_acs]

@Apple_Orange The above solution should give you only the desired output you provided , that is one row with Perl, I mean it will not include Apple in the output. are you getting a different result? 

[Qiu · Accepted solution]

@Apple_Orange 
Basically same approach as @binu_acs .
Added one unique tool to make sure the type will be unique, though the result will be the same but just want to avoid unnecessary data generated.

[Apple_Orange]

Thanks so much, it works

[Apple_Orange]

Thanks so much Qiu, Really appreciated for the solution. I have accepted it as solution