Joining Tables Using Fuzzy logic in Qlik Script

[Anonymous]

I want to join two large tables based on address. However, the match does not need to be precise, just needs to be close. What is the easiest way to do this in the Qlikview script?  I tried using Excel Fuzzy match add-in but that breaks if your files are big. Qlik would be ideal.  I tried reading through other posts but I ma having trouble following them. looks like the situations outlined there are a little different from mine. Anyway, thanks all.

[marcus_sommer]

A fuzzy logic couldn't be applied by any join but you could get quite near with a mapping-approach and mapsubstring(). But even with this you need to define which parts of the fields needs to be matching and also that you could get multiple matchings.

Therefore I suggest before thinking about it try to clean and to adjust both fields for example by using trim() and keepchar/purgechar() to clean the fields, upper/lower() to make both writings equally, checking the lengths of strings and adjusting the order of the content - maybe at first ZIP then city and street or whatever. Everything what you could do on this part will make the following mapping-approach easier and with lesser errors.

Here some links to what is meant:

Data Cleansing

Re: Merging array to table

Re: finding dynamic text sequence within a larger text field

Re: for each loop

- Marcus

[Anonymous]

Marcus,

Thanks for a quick reply but here is my challenge. suppose an address field in table 1 is this: 320 Park Avenue Worcester  and the address field in table 2 says: 320 Park Ave Worcester. The only difference is the way Avenue is spelled. I want to be able to identify where the address values are this similar. Not sure the mapping you are referring helps with that but I may be wrong.

[Anonymous]

On second thought, I see what you are saying. I can make the common field as similar possible and then when I do an exact match, I will get decent results. I see.