By the end of this chapter you will be able to:

• Use a merge table with data enrichment via a lookup pipe.

In this exercise you will merge some address information and in the same table you will further enrich the data by looking up the postcode for each record.

Merge address and customer category data

Merge addresses and customer categories stored in the database:

1. Create a new model called Build full customer details
2. Drag on the

   Insert excerpt
   _datasource _datasource nopanel true

    CRM
3. Create database collectors and tables

   Insert excerpt
   _database_collector _database_collector nopanel true

   s and

   Insert excerpt
   _tables _tables nopanel true

   s to read data into PhixFlow from the following tables in the CRM datasource:
   1. SOURCE_ADDRESS_LIST
   2. SOURCE_CC_LIST
4. Merge these two tables:
   1. Call the merge table Full Addresses
   2. Merge on the attribute NAME from both input tables.
   3. Drag other attributes from the input tables into the merge table so that you have a full set of data for each customer.
5. In the merge table, for the attribute NAME set the expression to: _key[1]
6. Run analysis on Full Addresses and view the results.

Enrich addresses with postcodes

You will now further enrich the address data with postcodes:

1. Again using the datasource CRM, create a database collector and table to load into PhixFlow data from the table SOURCE_POSTCODE_LIST
2. Create a lookup pipe from the new table SOURCE_POSTCODE_LIST to Full Addresses
3. Call this lookup pipe, pc
4. Click 

   Insert excerpt
   _save _save nopanel true

    to save the pipe settings.

5. In the Order/Index attributes for the pipe create the following:

   Left-hand (post code reference data - SOURCE_POSTCODE_LIST)	Right-hand (output - Full Addresses)
   HOUSENAMEORNUMBER	HOUSENAMEORNUMBER
   STREETNAME	STREETNAME
   TOWN	TOWN
   COUNTY	COUNTY

   
   

6. This is illustrated here:
7. Image AddedIn the table Full Addresses, create a new attribute called Postcode and set its expression to be pc.POSTCODE
8. This is illustrated here:
   1. Image RemovedImage Added
9. Run analysis on Full Addresses and view the results.
10. In the model window, click 

    Insert excerpt
    _save_saveModel _save_saveModel nopanel true

    .