6 Enriching Data Sets

Owned by Chris Welford
Last updated: Jun 08, 2023 by Zoe Baldwin
3 min read


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

  • Create lookup pipes
  • Enrich a data set using a lookup pipe

Copy data from merge

In your Introduction to Modelling analysis model you will create a new table to copy over all data from your merge table:

  1. Hover over the merge table Customer All Details, and press  Create New Table.
  2. Call this table: Customer Details with Region.
  3. To copy all attributes into your new table, hover over Customer All Details and in the hover menu press  Show attributes for <table>
    1. Select all attributes in the list:
      1. Select the tick box next to Name.
      2. Alternatively, select the top attribute, then keeping shift pressed down, select the bottom attribute in the list.
    2. Drag the attributes on top of the table Customer Details with Region.
  4.  Run Analysis on new Customer Details with Region table and look at the data in it to verify that you have copied the data from the Customer All Details merge table.
  5. Remember to save your model layout.

Here you have copied data from a merge table into a new table – but this method works for any type of input table. Copying data into a new table is a common starting point for a modelling step.

Create a lookup into reference data

Now you will add a lookup pipe from Regions by County to enrich the data in Customer Details with Region, by finding the region for each customer address:

  1. Hover over the table Regions by County and press  Connector.
  2. Move the pointer over the top of Customer Details with Region and select – this will attach the end of the pipe to the selected table.
  3. In the pipe settings, enter:
    • Name: region
    • Type: Look-up
    • Data To Read: leave as the default – Latest
    • Press  Apply.
    • In the Order/Index section of the pipe settings we will set up the fields to match across the two recordsets:
      1. Press  Show Attributes.
      2. Drag the attribute County from the left hand list (the input attributes from the table Regions By County) into the list of Order/Index attributes.
      3. Drag the attribute County from the right hand list (the output attributes from the table Customer Details with Region) on top of the Order/Index attribute County.
        1. Notice that in the Order/Index section, the Index Expression column has updated to _out.County.

        2. This means that the lookup will match records in the reference data (Regions By County) where County equals County in the output data (Customer Details with Region).
      5. Press  Apply and Close on the pipe settings.

    • Note that the pipe linking Regions By County to Customer Details with Region is dashed; this indicates that this is a lookup pipe.

When adding lookup pipes, the arrow points in the direction that the data flows. Here you are getting data from Regions By County to add to Customer Details with Region.

Enrich a dataset using a Lookup Pipe

Now you will add the region from Regions By County to Customer Details with Region:

  1. Hover over Regions By County and in the hover menu press  Show attributes for <table>
  2. On the list of the table attributes, drag the attribute Region from Regions By County on top of the Customer Details with Region table in the model.
  3. Run analysis on Customer Details with Region.
  4. View the data in the table. Check that the Region attribute has been populated with the value looked up from Regions By County, like the image below:
  5. In the model window, click  Save.