Grouping and matching

[ajayb]

Hi Alteryx Community,

I will appreciate all the help I can get on this pressing problem.

I have a large dataset (>50k rows) and for this I need to apply the below rules:

I want to perform a check where - for the records with the same 'Material Id.',

Alteryx would look at the manufacturers and suppliers and for every row for that material, if there is a one manufacturer tied to a different manufacturer, add a 'YES' under the Delete Record column and 'NO' for all other cases.

The idea is that one manufacturer cannot supply the same part from a different manufacturer who also manufactures the same part.

I added the below table with examples and attached a spreadsheet with the same.

Record Id.	Material Id.	Supplier	Manufacturer	Delete Record?
1	Part_A	Supplier_1	Manuf_ABC	NO
2	Part_A	Supplier_2	Manuf_ABC	NO
3	Part_A	Manuf_ABC	Manuf_DEF	YES
4	Part_B	Supplier_1	Manuf_XYZ	NO
5	Part_B	Manuf_DEF	Manuf_KLM	NO
6	Part_C	Supplier_2	Manuf_KLM	NO
7	Part_C	Supplier_3	Manuf_KLM	NO
8	Part_C	Manuf_KLM	Manuf_GHI	YES
9	Part_C	Manuf_GHI	Manuf_GHI	NO

[T_Willins · Accepted solution]

Hi @ajayb,

Workflow attached.  The last Join tool removes the unwanted rows on the join output, so the left output gives you the data you want.

[ajayb]

This is a great solution. Thank you for the quick response!

Is there a way to identify the rows that need to be deleted?

I was thinking about a new column that just says 'Yes' or 'No' for each row. This way I can keep a record of the rows that were identified for deletion before taking the final step of deleting them.

[T_Willins · Accepted solution]

Since the data to be deleted is in the J output of the last Join tool, you get your desired results by adding Formula tools to add the new field, a Union tool to bring it all together, and a Sort tool to put it back in the original order.