Reverse Duplicate

Hi Sri,

How about trying something like this.

First make sure you remove all the duplicates.  First add a virtual field call record number using the @precno function.  This way when we are doing the analysis we can trace the record back to the original file. 

Then perform Duplicate Key Detection using the ID and ID1 fields as the key.

In your example it will show that records 15, 17, 18 and 20 are duplicates and should be removed from the file.

You can remove them by doing a join with the mod file being the primary and the duplicate file being the secondary.  The match is on the record number and you use the Records with no secondary match.

So now we have a file with no duplicates.

Now comes the fun part, I finally decided to create a virtual field adding the ID and ID1 fields together.

This gives a unique key that will be the same if the ID + ID1 is the same as any other ID + ID1

I then did a summary by ID_ADD and in the Fields I selected the REC_NO to be included and I used the Use fields from last occurrence option.  This will give me the record number of the second transaction when there is a match.

So the results shows the REC_NO we must remove from the main file.

In this case they are all duplicates, if there are any non-duplicates then we would have to do one additional step by extracting NO_OF_RECS = 2.  We join this file back to the main file using the REC_NO as the match and Records with no secondary match as the match option and hopefully you will now have a file with no duplicates.

Let me know how this works out.

Brian

I am thinking this is the way to go but I undersand the possible problem as the numbers could be duplicated.  So why not use an algorithm to change the numbers to something that would be more unique.  My suggestion would be to take the first ID and multiple each number of that ID by another number and concatenate them together.  In this example I used the ASCII code of the number as the multiplier.  So if you have the following:

ID 21771 ID1 21318

would be

(2 * 50) & (1 * 49) & (7 * 55) & (7 * 55) & (1 * 49) =

100 & 49 & 385 & 385 & 49 =

So your new ID would be 1004938538549, you do the same for ID1 and then add them together so the ID would really be unique.  Do you think that would work?  If not I think it comes down to creating a script that will take the first row, look through the rest of the file for a match, save the match record number to a another file, then do the second row and so on.  You then use the file to do a join to remove all the duplicates.