Filter records given a certain context

Esmeralda_In_Paris

Hi Everyone,

I am dealing with a huge file (more that 300000 rows and more than 200 fields).

I need to filter the rows that have the same customer, same division and same year.

here is a sample file with the 4 fields that need to be filtered.

Could anyone help me with it?

thank you

Esme

Sample.csv

Preparation

Accepted answers

_NB

If you have more than two instances of this set of data (more than one duplication) in your dataset, the additional Group By will give you a list of the unique duplicates for the Customer, Year, Division combo. Using this to rejoin will provide a list of unique record IDs that are duplicate. If you rejoin without a Group By, the joined result will use ALL the duplicate results to join on the original dataset making the results (N*(N+1)) large. You can still use another Group By on all fields after the fact, but it will make your workflow much faster if you slim down your data before the join. See attached.

Duplications 2.jpg

Sample.csv

Duplications.yxmd

All comments

mathieuf

Hi @Esmeralda_In_Paris,

I'm not 100% of your need. Is that what you are looking for:

duplicates.yxmd

_NB

Use the Unique tool to find unique values from the Customer, Year, and Division fields. Use the Duplicate output to rejoin on the Customer, Year, and Division fields and deselecting the duplicate fields from the join output. There were no duplications in your provided dataset, so I had to create one.

Sample.csv

Duplications.yxmd

Esmeralda_In_Paris

bonsoir Mathieu

merci pour votre aide

good thing I learnt: I did not know I could use a file in Left and right join with the filter tool ! Good to know

but unfortunately, the formula [recordID]!=[right_recordID] brings no common ID.

_NB

There was one more thing I forgot to mention that I just thought of. Add a GROUP BY on the Customer, Year, and Division fields between the Unique and Join tools before rejoining with the original data. This will create a list of unique duplicates present in the data in case there are more than two instances of this set of data.