I have these 10 data fields and would like to create a formula to identify potential duplicates with an index score of how strong the match is between any 2 records. For example if 10 of 10 match the two records would be flagged as 100% match. Here are the fields.
IndividualId (UniqueID),
FirstName,
MiddleName,
LastName,
PreferredName,
BirthDate,
PrimaryEmail,
Home_PhoneNumber,
Mobile_PhoneNumber,
Address1_PrimaryAddress
I would like to do this in a Qlik table during the load script if possible. I've tried, and tried, and tried, this concept of creating the Individuals_Data table, a second table with same fields as in IndividualIdDup, FirstNameDup, ...etc. and then trying to join the tables with a where clause (where IndividualId<>IndividualIdDup). I've tried that in several different ways, but the where clause always fails - unable to find IndividualId or whichever field used to keep it from potential duplicate matching of a record to itself.
Tried it with record numbers or row numbers using similar where clause, but it fails. If anyone has an idea, I would love it!
// Load your data
Individuals_Data:
LOAD
RowNo() as RecNo,
IndividualId,
FirstName,
MiddleName,
LastName,
PreferredName,
BirthDate,
PrimaryEmail,
Home_PhoneNumber,
Mobile_PhoneNumber,
Address1_PrimaryAddress
FROM [myfile]
(txt, utf8, embedded labels, delimiter is ',', msq);
// Use Resident load to compare each record with all other records
DuplicateCheck:
LOAD
RowNo() as RecNo_Dup,
IndividualId as IndividualIdDup,
FirstName as FirstNameDup,
MiddleName as MiddleNameDup,
LastName as LastNameDup,
PreferredName as PreferredNameDup,
BirthDate as BirthDateDup,
PrimaryEmail as PrimaryEmailDup,
Home_PhoneNumber as Home_PhoneNumberDup,
Mobile_PhoneNumber as Mobile_PhoneNumberDup,
Address1_PrimaryAddress as Address1_PrimaryAddressDup
Resident Individuals_Data;
// Join the original data with the DuplicateCheck table to compare each record with its duplicates
Join (Individuals_Data)
LOAD
RecNo,
IndividualIdDup,
FirstNameDup,
MiddleNameDup,
LastNameDup,
PreferredNameDup,
BirthDateDup,
PrimaryEmailDup,
Home_PhoneNumberDup,
Mobile_PhoneNumberDup,
Address1_PrimaryAddressDup,
RecNo_Dup
Resident DuplicateCheck
Where RecNo <> RecNo_Dup and (
IndividualId <> IndividualIdDup or
FirstName <> FirstNameDup or
MiddleName <> MiddleNameDup or
LastName <> LastNameDup or
PreferredName <> PreferredNameDup or
BirthDate <> BirthDateDup or
PrimaryEmail <> PrimaryEmailDup or
Home_PhoneNumber <> Home_PhoneNumberDup or
Mobile_PhoneNumber <> Mobile_PhoneNumberDup or
Address1_PrimaryAddress <> Address1_PrimaryAddressDup
);
// Calculate the percentage of matching fields
Individuals_Data:
LOAD
*,
(
(If(IndividualId = IndividualIdDup, 1, 0) +
If(FirstName = FirstNameDup, 1, 0) +
If(MiddleName = MiddleNameDup, 1, 0) +
If(LastName = LastNameDup, 1, 0) +
If(PreferredName = PreferredNameDup, 1, 0) +
If(BirthDate = BirthDateDup, 1, 0) +
If(PrimaryEmail = PrimaryEmailDup, 1, 0) +
If(Home_PhoneNumber = Home_PhoneNumberDup, 1, 0) +
If(Mobile_PhoneNumber = Mobile_PhoneNumberDup, 1, 0) +
If(Address1_PrimaryAddress = Address1_PrimaryAddressDup, 1, 0)
) / 10
) * 100 as MatchPercentage
Resident Individuals_Data;
// Drop intermediate tables
Drop Table DuplicateCheck;
// Output the final data
Data_Final:
LOAD *
Resident Individuals_Data;
// Drop the original Data table
Drop Table Individuals_Data;
