Address Validation Rule

 

Address validation (also known as address verification) is a rule that ensures street and postal addresses exist. An address can be verified in one of two ways: upfront, when a user searches for an address that is not correct or complete, or by cleansing, matching and formatting data in a database against reference postal data.

In the application, there are changes in:

  • Staging Configuration (Profiling section)
  • Rule Detail (Workbench)

Staging Configuration for Address Validation Rules

NoteThis is going to be required for first time setup on Oracle source only. Also, it is mandatory to setup this, otherwise on AV rules execution, user will be notified by an error message in remarks of rule that "MM configurations missing'.

mceclip0.png

mceclip1.png

Step 1 :  Navigate to Profile -> Profiling -> Select an Oracle data source, select main table and staging configuration table. In the below screenshot, main table is CUSTOMER_ADDRESSES and staging table is CUSTOMER_ADDRESSES_DVSUM. 
Note:  It is required to make sure that Staging table exists for AV rule to be run. 

1.jpg

Step 2 : Select main table and click on show details

2.jpg

Step 3 : Select Staging configuration tab and click on Edit

3.jpg

Steps 4 : Setting up Configuration, Additional Configuration, Staging Configuration fields 
Configuration setup
select Subject Area, Golden Record Criteria, check Enable Validation, provide Description
Additional Configuration setup
check Write-back Post Process i.e if user wants to enable write-back on main table, Case type i.e if user wants to keep the format of Suggested Address as it is fetched by default, in lower or UPPER case.

4.jpg
Staging Configuration setup
select Staging table, Match key, Match count, Match rule, Record identifier, Golden record ref, Record type, Merge status, Time stamp identifier and hit Save button given on top as shown below; 

 

4.1.jpg

Step 5 : On Saving the changes, user will be notified that changes are saved successfully. Also, an error message will be thrown displaying that "Fields are not mapped to address data types Address Line 1 or Country" and on bottom, Edit Mapping grid starts appearing as shown below ;

5.1.jpg

 

Edit Mapping

Step 6 : Now once staging configuration is set, click on Edit button to edit mapping. Because at first, randomly Match Data Type gets assigned to column names, so user is required to update those Address Match Data Types w.r.t required columns. 
User can select all columns by clicking Select All or can Include individual columns as shown below; 

6.jpg

Step 7 : Columns are mandatory to be mapped against following Address Match Data Types

  • City
  • Country
  • State
  • US ZIP code
  • ADDRESS LINE 1

Mapping Rule for Address Validation

Step 8 : If user has set Multi mapping (ADDRESS1, ADDRESS2, ADDRESS3) against Address Line 1, then user has to set a write-back column for mapping to Address Line1.
(In other words; Address from ADDRESS1, ADDRESS2, ADDRESS3 will be combined and considered as full address from user. Now this combined Address requires to be mapped on single column to Address Line 1)
Mapping Rule for Address Validation can be done by following options;

  • Write-Back column: 
    Select write-back column from given options and Save Changes as shown below; 
    8.jpg

  • Add Criteria: 
    Select column, filter, value.
    Is True: If this condition passes then select address from CUST_STREET_ADDRESS1 and ignore others. 
    Is False: If above conditions fails, then select from CUST_STREET_ADDRESS2, CUST_STREET_ADDRESS1 and choose write-back column as well. 

8.2.jpg

 

Address Validation Rule 

Step 9 : Go to Manage Rules, Click Add Rule, Select Address Validation rule from Process Quality.

9.jpg

Step 10 : Provide all the required fields here. 
Note: Table name field should be Main table not the staging configuration table as Rule will be created on Main table.

10.jpg

 

Step 11: Go to Rule detail page and Run the rule as shown below; 

11.jpg

Step 12: Once Rule is executed, following data is fetched; 

  • Run Status 
  • Scanned Records (Total records got scanned)
  • Requires Review (Not OK results)
  • Fixed Records
  • Readiness Score
  • Remarks

12.jpg

Step 13 : Go to Addresses tab, addresses that got matched will be displayed under the grid. If user wants to edit/update any record, can be done by Clicking on Review button as shown below;

  • ADDRESS1, ADDRESS2, ADDRESS 3 are mapped in Address Line 1 column. 
  • Address Line 1, City, state, country are mapped together in Complete Address column.
  • Complete Address detail information is fetched from User's DB

13.jpg

 

Match Statuses 

Step 14  Following are the Match statuses 

14.jpg

  • Incomplete (If any of the following are missing i.e country, Address Line 1, ZIP code and city (both of them))
    1) Accept and Skip suggestion button on main page will be disabled.
    2) Current and Suggest address radio buttons will also be disabled on review window. 
  • No Match (If google does not suggest a concrete address)

    mceclip0.png

    1) Accept and Skip suggestion buttons will be disabled on main page 
    2) Current and Suggested address radio buttons will be disabled on Review window 

  • Enrich (Match Found but with slight modification in Suggested Address)
    mceclip1.png

  • Standardize (Match Found but in Uppercase or used standardized short terms)
    mceclip2.png

    mceclip3.png

    1) Standard and Enrich are same 
    User can update by following ways;
    a) User can Accept and Skip Suggestion from main page
    b) User can update from Drop down options 
    c) User can update from Review window (Suggested Address option >> Accept,  Current option >>Skip) 

    mceclip4.png

    mceclip5.png

  • OK (Perfectly Matched)
    1) Accept and Skip suggestion button on main page will be disabled.
    2) Current and Suggest address radio buttons will also be disabled on review window. 

ok.jpg

 

Record Statuses

Step 15

There are following record statuses; 

  • Update (If user Accept Suggestion)mceclip8.png
  • Skip (If user Skips Suggestion) 
    mceclip10.png

  • Modified update (Once current address fetched from DB gets updated and Saved by user)
    mceclip7.png

    User can Edit current address from review window, validate it. If found then success message will be displayed otherwise error message will be displayed. Also, "Edited" text right next to Current address label will show. User can also reset the value to the original value. 

    mceclip6.png

Step 16  Once record statuses gets Modified update, Update, Skip then count for Pending records decreases and Count for Reviewed records increases. 

mceclip11.png

 

Step 17  User can Select all records and perform any action (Accept or Skip suggestion), clear all records selection or Discard.

mceclip12.png

Step 18  Once user clicks Finish button given on bottom, then Commit button appears in Address tab on rule detail page. 

mceclip13.png

mceclip14.png

 

Step 19 Once commit is done, then Fixed records and Reviewed records count gets updated in Rule summary

mceclip16.png

 

Step 20 User can also create view 

mceclip18.png

Step 20  User can also add comments on Match Status column. For that, click on any status, add comment, and Save it. Comment icon with count starts appearing on main grid. The comment count also increases with every new comment added. If there's no comment then on hovering comment icon appears. 

mceclip19.png

mceclip20.png

 

 

Postal Addressing Standards supported in Address Validation rule 

1) Please find the postal Addressing acronyms list1. On this link, all the acronyms showing there are supported. 

2) In this list2, Primary street and Postal service standard suffixes are supported.

mceclip0.png 

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk