Duplicates

[MZ900605]

hello, I have a question if we have a table that has duplicates how can we eliminate the duplicate that has fewer data.

ABC	1	[Null]	[Null]	6	2
ABB	4	6	7	1	3
ABC	1	2	[Null]	6	[Null]
ABC	1	2	8	6	2

the criteria of eliminating duplicates using the unique tool are it by what is first is kept?

[atcodedog05]

Hi @MZ900605 

Here is how you can do it. I have added one more row of data.

Workflow:

1. Using the formula I am calculating 2 fields. Sum of fields and count of nulls.

2. Using sort tool by ascending count of nulls (so that rows with less null comes up) and descending by sum.

3. Using sample keeping only first occurrence of rows based on field1.

Hope this helps : )

[MZ900605]

Hi, Thanks for your help but one thing is different some of the fields are characters and names not only numbers.

[atcodedog05 · Accepted solution]

Hi @MZ900605 

Got it. Then lets remove the sum part and keep only the null count part.

Workflow:

Hope this helps : )

[OllieClarke · Accepted solution]

@atcodedog05 @MZ900605 If you set your data type to a numeric (e.g. int16) then ISNULL() will output -1 for True and 0 for false. This means you don't have to write the IIF()s but can sort by the summed result descending 🙂

[ewassell]

This is awesome! Can you tell me how I can make an output file that shows me the records that were pulled as duplicates?