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 stream to copy over all data from your merge:

• Hover over the stream Customer All Details, and press  - Add a new Stream
• Call this stream: Customer Details with Region
• Copy all attributes into your new stream:
  • Hover over Customer All Details and in the hover menu press  - Show Stream Attributes
  • Select all attributes in the list:
    • Select the top attribute
    • Keep shift pressed down and select the bottom attribute in the list
  • Drag the attributes on top of the stream Customer Details with Region
• Run your new stream and look at the data in it to verify that you have copied data from the merge stream

• Remember to save your model layout

  Here you have copied data from a merge Stream into a new stream – but this method works for any type of input stream. Copying data into a new stream 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:

• Hover over RegionsByCounty and press - Add a new Connector
• Move the pointer over the top of Customer Details with Region and select – this will attach the end of the pipe to the selected stream
• In the pipe details form that pops up:
  • Enter the Name: region
  • Type: Look-up
  • Data To Read: leave as the default – Latest
  • Press - Apply
  • In the Order/Index section we will set up the fields to match across the two data sets:
    • Press  - Show Source and Target Attributes
    • Drag the attribute County from the left hand list (the input attributes from the stream RegionsByCounty) into the list of Order/Index attributes
    • Drag the attribute County from the right hand list (the output attributes from the stream Customer Details with Region) on top of the Order/Index attribute County
      • You should see the Index Expression of the Order/Index attribute update to _out.County
      • This means that the lookup will match records in the reference data (RegionsByCounty) where County = County in the output data (Customer Details with Region)
    • Press 
  • Note that the pipe linking RegionsByCounty to Customer Details with Region is dashed; this indicates that this is a lookup pipe
  • When adding lookup pipes remember that the arrow points in the direction that the data flows; here you are getting data from RegionsByCounty to add to Customer Details with Region

Enrich a data set using a Lookup Pipe

Now you can add the region – from RegionsByCounty – to Customer Details with Region:

• Hover over RegionsByCounty and in the hover menu press  - Show Stream Attributes
• The list of the stream attributes will pop up
• Drag the attribute Region from RegionsByCounty on top of Customer Details with Region
• Run analysis on Customer Details with Region
• Have a look at the data in this stream – check that the Region attribute has been populated with the value looked up from RegionsByCounty