how to compare two fields and mark it as perfect if 90 percent of data in two fields matches

[naveen341]

how to compare two fields and mark it as perfect if 90 percent of data in two fields matches

HI Have two different fields coming from two different sources.

When i do comparision i would like to mark it as perfect when 90% of the data matches how can we do this kind of comparision.

Ex: data 1: 1044 lakehurst drive,Apt #103, waukegan ,il

Data 2: 1044 lakehurst dr Apt  103 waukegan ,il.

O/p Data1=data2.

Regards,

Naveen.

[migueldelval]

Hi naveen,

Maybe you can use functions as len, evaluate, etc to compare both fields.

If you attach a set of data, I could help you.

Regards

Miguel del Valle

[Anonymous]

Hi naveen,

You can use purgechar function and upper to compare it:

PurgeChar(Upper(data),'ªº!"·$%&/()=?¿^*¨\|@~$€¬[]{}#,.;- '&chr(39)&chr(10)&chr(19),'')

With this, i have deleted all special chars, inluding tabs, returns and quotes. I return data in uppercase format too.

Regards!

[naveen341]

this helps in eliminating special characters but some time i get the data like street vs st, drive  vd dr, road vs RD, corp vs cor logically bith are equall but when we do comparision over dr in qlikview its not equall how to deal with this data

[naveen341]

Sales order    Ship To Name (Data source A)                            Ship To Name (Data source B)

012345665     AMERISOURCEBERGEN DRUG CORPORA     AMERISOURCEBERGEN DRUG CORPORATION.

I Want to make these both ship to names as match when 90 percent of data matches.

Can you please help me

[Anonymous]

Hi naveen,

QlikView doesn't have functions like that you want, for compare street with st, I think that the better is to create a dictionary with excel to translate, word by word.

Regards!

[swuehl]

Maybe you can use something like the Levenshtein distance to calculate a number for the similarity:

Levenshtein Distance VBScript