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.
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
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.
Does anybody have a work-around to avoid confusing the Data Compare when there are duplicate comparison keys? Thank you!