10 Merging Recordsets with enrichment
- Chris Welford
- Anthony George
Owned by Chris Welford
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:
- Create a new model called
Build full customer details
- Drag on the datasource
CRM
- Create database collectors and tables to read data into PhixFlow from the following tables in the
CRM
datasource:SOURCE_ADDRESS_LIST
SOURCE_CC_LIST
- Merge these two tables:
- Call the merge table
Full Addresses
- Merge on the attribute
NAME
from both input tables. - Drag other attributes from the input tables into the merge table so that you have a full set of data for each customer.
- Call the merge table
- In the merge table, for the attribute
NAME
set the expression to:_key[1]
- Run analysis on
Full Addresses
and view the results.
Enrich addresses with postcodes
You will now further enrich the address data with postcodes:
- Again using the datasource
CRM
, create a database collector and table to load into PhixFlow data from the tableSOURCE_POSTCODE_LIST
- Create a lookup pipe from the new table
SOURCE_POSTCODE_LIST
toFull Addresses
- Call this lookup pipe,Â
pc
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
- This is illustrated here:
- In the table Full Addresses, create a new attribute called
Postcode
and set its expression to be pc.POSTCODEÂ - Run analysis on
Full Addresses
and view the results.