Issue with Data Compare with Duplicate Comparison Keys

[JesseJamesP]

Hello!

I was just tinkering with the Data Compare feature in Toad Data Point. This seems to be an insanely useful tool, except for one caveat, the comparison can be a bit wonky when the comparison key isn’t unique. The data sets I have only have one field to be used as a comparison key as I need to compare all the other fields where the comparison key is the same… I have duplicate “comparison keys” because of slight variations in the input of the person’s name, etc. These are among the things we are looking to correct.

Example:

Data Set 1:
Comparison Key | First Name | Last Name | DOB
1234567890 | John | Doe | 01/02/1959
1234567890 | Jon | Doe | 01/02/1959
0987654321 | Bob | Smith | 08/07/1975

Data Set 2:
Comparison Key | FirstName | LastName | Date of Birth
1234567890 | John | Doie | 01/04/1964

etc, etc.

When we have a duplicate comparison key, it shows up in both the ‘Target Only’ and ‘Source Only’ types, when it is actually found in both data sets. It would be preferable if there was an additional type that took this situation into consideration, such as “Multiple Matches”, to at least indicate that the comparison was not possible.

TLDR;
Does anybody have a work-around to avoid confusing the Data Compare when there are duplicate comparison keys? Thank you!

[Debbie_Peabody]

Data Compare is really counting on the key to be unique. See description and hint below. It explains how it will respond. How many rows in the table? If small you could consider making a new table and with a select add a unique number to the key column so they are all unique. Just an idea. It just depends on if you this is a one time thing or you are trying to compare periodically over time.