topic Re: Comparing two string tables in QlikView

Comparing two string tables

Thu, 07 May 2015 09:35:28 GMT

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

Re: Comparing two string tables

robert_mika — Thu, 07 May 2015 10:10:20 GMT

Is just the Middle initial that is different or you have got more examples?

Re: Comparing two string tables

Thu, 07 May 2015 10:19:25 GMT

Could be multiple things, spelling errors, additional names, transpositions of letters etc.

Re: Comparing two string tables

Gysbert_Wassenaar — Thu, 07 May 2015 10:25:05 GMT

Perhaps this blog post helps: Teaching the load script new tricks

But perhaps you should use a built-to-purpose data cleansing tool.

Re: Comparing two string tables

Thu, 09 Jul 2015 08:46:00 GMT

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