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

First, 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 

   Insert excerpt
   _table_new _table_new nopanel true

   .
2. Call this table: Customer Details with Region.
3. Copy To copy all attributes into your new table:Hover , hover over Customer All Details and in the hover menu press 

   Insert excerpt
   _table_attributes_show _table_attributes_show nopanel true

   1. Select all attributes in the list:
      1. Select the tick box next to Name.
      2. Alternatively, select the top attribute.Keep , then keeping shift pressed down and , select the bottom attribute in the list.
   2. Drag the attributes on top of the table Customer Details with Region.
   Run your new

4. Insert excerpt
   _run_analysis _run_analysis nopanel true

    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.

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 RegionsByCounty and the table Regions by County and press 

   Insert excerpt
   _pipe _pipe nopanel true

   .
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 details form that pops upsettings, enter:
   • Name: region
   • Type: Look-up
   • Data To Read: leave as the default – Latest
   • Press 

     Insert excerpt
     _save _save nopanel true

     .
   • In the Order/Index section of the pipe settings we will set up the fields to match across the two recordsets:
     1. Press 

        Insert excerpt
        _attributes_show _attributes_show nopanel true

        .
     2. Drag the attribute County from the left hand list (the input attributes from the table RegionsByCountyRegions By County) into the list of Order/Index attributes.
     3. Drag the attribute County from  from the right hand list (the output attributes from the table Customer Details with Region) on top of the Order/Index attribute County.
        1. Image Added
        In
        1. Notice that in the Order/Index section,
        you should see that
        1. the Index Expression column has updated to _out.County.
        County 
        1. 
Image Modified

        2. This means that the lookup will match records in the reference data (
        RegionsByCounty
        1. Regions By County) where County
        equals County
        1. equals County in the output data (Customer Details with Region).
     4. Press 

        Insert excerpt
        _finish _finish nopanel true

         on the pipe settings.

   • Note that the pipe linking

     RegionsByCounty to

     Regions By County to Customer Details with Region

     is

      is dashed; this indicates that this is a lookup pipe.

Enrich a data set dataset using a Lookup Pipe

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

1. Hover over RegionsByCounty and Regions By County and in the hover menu press 

   Insert excerpt
   _table_attributes_show _table_attributes_show nopanel true

   .
2. The On the list of the table attributes will pop up.Drag , drag the attribute Region from RegionsByCounty on  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 RegionsByCounty. Regions By County, like the image below:
   1. Image Added
5. In the model window, click 

   Insert excerpt
   _save_saveModel _save_saveModel nopanel true

   .