Match Records on Number in the Address Line

Introduction

Usually Address Line 1 in the customer data contains house or flat number and we can safely match records using these numbers if postcodes are provided. This approach can significantly improve the match rate with minimum risk of false matching. 

VB Expressions

You can easily extract the number and use it for matching in Flat File Checker with those functions:

• LeadingNumber(string) - gets the number from the beginning of the string if it starts with number, returns original string otherwise;
• TrailingNumber(string) - gets the number from the end of the string if it ends with number, returns original string otherwise;
• GetNumbers(string) - gets all numbers from the string if it contains numbers, returns original string otherwise.

Address Line Example

Original file with customer details contains [Address 1] field. From this field we want to create a derived field that will contain either leading number or the original value if address line does not start with number. To create a field:

• Right click in the Fields area on the Main form and select Add->Calculated Field in the pop-up menu;
• Fill in field details following example on the Figure 1;

Figure 1. Extract Leading Number from Address Line.

Match Records

Unique constraint must be used to match records using our new derived field: 

Figure 2. Unique Constraint Form.