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

...

• Create Lookup Pipeslookup pipes
• Enrich a data set using a Lookup Pipe

...

• lookup pipe

Copy data from merge

First, you will create a new Stream stream to copy over all data from your merge:

• Hover over the stream Customer All Details, and press Image Removed press Image Added - Add a new Stream
• Call this Streamstream: Customer Details with Region
• Copy all attributes into your new Streamstream:
  • Hover over Customer All Details and in the hover menu press Image Removed press Image Added - 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 stream and look at the data in it to verify that you have copied data from the merge Streamstream

• Remember to save your model layout

  Here you have copied data from a merge Stream into a new

  Stream

  stream – but this method works for any type of input

  Stream

  stream. Copying data into a new

  Stream

  stream is a common starting point for a modelling step.

  Anchor_Toc442889059_Toc442889059

  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 Image Removed Image Added- 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 Streamstream
• In the Pipe Setup pipe details form that pops up:
  • Enter the Name: region
  • Type: Look-up
  • Data To Read: leave as the default – Latest
  • Press Press Image Modified - Apply
  • In the matching attributes form that pops up create a match:Order/Index section we will set up the fields to match across the two data sets:
    • Press Image Added - Show Source and Target Attributes
    • Drag the attribute County from the left hand box list (the input attributes from the stream RegionsByCounty) into the list of matching Order/Index attributes
    • Drag the attribute in_ County from the right hand box into the list of matching attributes – on top of the entry for County you created abovelist (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 = in_ County in the output data (Customer Details with Region)
    • Press 
  • A Note that the pipe will appear linking RegionsByCounty to Customer Details with Region - note that the pipe is dashed; this indicates that this is a lookup pipe
  • When adding Lookup Pipes 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 Image Removed press Image Added - Show Stream Attributes
• The list of the Stream stream attributes will pop up
• Drag the attribute Region from RegionsByCounty on top of Customer Details with Region
• Run Analysis analysis on Customer Details with Region
• Have a look at the data in this Stream stream – check that the Region Attribute attribute has been populated with the value looked up from RegionsByCounty