Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello
Is it possible to compare the accuracy of two string fields?
EG:
I have two client tables but the naming in the two is different, I want to identify them
Table 1 Client Name: John Black
Table 2 Client Bank Acc Name: John H Black
Its the same client captured differently
Thanks in advance for the help
Is just the Middle initial that is different or you have got more examples?
Could be multiple things, spelling errors, additional names, transpositions of letters etc.
Perhaps this blog post helps: Teaching the load script new tricks
But perhaps you should use a built-to-purpose data cleansing tool.
Just to identify the not matching names from both the tables, use below script
Tab1:
LOAD
Name,
1 as Tab1_Matched
FROM
Del.xlsx
(ooxml, embedded labels, table is Sheet1);
Tab2:
Join (Tab1)
LOAD Name,
1 as Tab2_Matched
FROM
Del.xlsx
(ooxml, embedded labels, table is Sheet2);
Not_Matched:
NoConcatenate
LOAD
Name,
Tab1_Matched,
Tab2_Matched
Resident Tab1
Where Not(Tab1_Matched = 1 and Tab2_Matched=1)
;
DROP Table Tab1;
Tab1_Matched, Tab2_Matched will let you know the table in which the name is available. After that you may have to do some manual work to cleanse the data. Or if you have logical steps to cleanse the data, then those can be converted to QV script. Let us know if you have those. Will try!
Regards,
KKR