How can we match two address fields one contains abbreviated form and other data set contains full name

[naveen341]

Hi .

Here i have two data sources for address one data source contains abbreviated form where as the other contains full name how can we do comparision for both of them and make them matched.

Ex.

123 Maple st,                        VS                  123 Maple Street,

Chicago ,IL                                                  Chicago,IL

Same way we have for Rd vs Road how can i Match these two fields any Suggestion.

Thanks In advance.

[satishkurra]

What is your expected output by comparing these 2 address fields?

[Gysbert_Wassenaar]

There are specialized tools for data cleansing. I recommend you purchase one of those tools. Trying to implement this in Qlikview is possible, but hard and error prone. You can use mapping tables for example or implement an algorithm like Soundex and or Metaphone. You can find examples on this site using the search feature. You may also want to join the Data Quality group.

[naveen341]

expected output is both matches or both are equall.

[jlongoria]

If I understand what you are trying to do, you first have to normalize all of your addresses so that you can later group/match address. US addresses vary greatly and it gets even more complicated from country to country.  If you will be using addresses for business logic, I STRONGLY suggest that addresses be normalized at INPUT TIME or that you extract addresses that are already normalized by the time you are using them in QlikVew.

Gysbert is correct, there are specialized tools/software for this. In general, you will have to normalize your addresses before you compare them. You should read up on Coding Accuracy Support System (CASS) certified software. This link has a good summary: CASS Certified Software and Services - Defined

Additionally, I found a United States Postal Service (USPS) Web Tools Wrapper here: United States Postal Service (USPS) Web Tools Wrapper - CodeProject  ‌You can try to use this wrapper to normalize. I have not used the wrapper so I do know what limitations/challenges you may face. Again, this is something you should try to do independently of what you are doing in QlikView.

If you really want create your own address normalization process in QlikView you can try simple logic but you should consider the complete list of US postal standards found here: http://pe.usps.gov/cpim/ftp/pubs/Pub28/Pub28.pdf

If you do end up having to normalize addresses, you should try to create a stand-alone process that normalizes all addresses THEN triggers your qlikview load scripts so that your load scripts use normalized addresses and you don't try to normalize addresses in QlikView.

If you absolutely want to use QlikView you *could* do as below (but just because you can doesn't mean you should):

1. Extract Data

      You can use QlikView for this

      Create a csv file with denormalized addresses and include other key attributes that relate to address (customer/user ID, for example)

2. Transform Data (I still suggest third party app/service for address normalization)

      In your case Normalize Addresses

      You can write a QV load script and EXECUTE command

      EXECUTE can trigger external batch file and/or call a third party app/service

      Store results in another csv file which will contain normalized addresses

3. Load transformed data with normalized addresses into QV App

      Read csv file created in step 2

4. Create visualization solution/app