hello, I have a question if we have a table that has duplicates how can we eliminate the duplicate that has fewer data. ABC1[Null][Null]62ABB46713ABC12[Null]6[Null]ABC12862 the criteria of eliminating duplicates using the unique tool are it by what is first is kept?

Duplicates

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?

In Database

Data Investigation

In-Database

Accepted answers

atcodedog05

Hi @MZ900605

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

Workflow:

Hope this helps : )

duplicates.yxmd

OllieClarke

@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 🙂

All comments

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 : )

duplicates.yxmd

MZ900605

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

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?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703