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:

1. Hover over the stream Customer All Details, and

...

1. press Image Added Add a new Stream
2. Call this

...

1. stream: Customer Details with Region
2. Copy all attributes into your new

...

1. stream:
   1. Hover over Customer All Details and in the hover menu

...

1. 1. press Image Added Show Stream Attributes
   2. Select all attributes in the list:
      1. Select the top attribute
      2. Keep shift pressed down and select the bottom attribute in the list
   3. Drag the attributes on top of the stream Customer Details with Region
2. Run your new

...

1. stream and look at the data in it to verify that you have copied data from the merge

...

1. stream

2. Remember to save your model layout

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

...

1. stream – but this method works for any type of input

...

1. stream. Copying data into a new

...

1. stream is a common starting point for a modelling step.

...

...

1. 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 press

...

1. Image Added Add a new 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

...

1. stream
2. In the

...

1. pipe details form that pops up:
   • Enter the Name: region
   • Type: Look-up
   • Data To Read: leave as the default – Latest
   • Press Image Modified Apply
   • In the

...

1. • Order/Index section we will set up the fields to match across the two data sets:
     1. Press Image Added Show Source and Target Attributes
     2. Drag the attribute County from the left hand

...

1. • 1. list (the input attributes from the stream RegionsByCounty) into the list of

...

1. • 1. Order/Index attributes
     2. Drag the attribute

...

1. • 1. County from the right hand

...

1. • 1. list (the output attributes from the stream Customer Details with Region) on top of

...

1. • 1. the Order/Index attribute County
        • In the Order/Index section, you should see that the Index Expression column has updated to _out.County
Image Added

        • This means that the lookup will match records in the reference data (RegionsByCounty) where County =

...

1. • 1. • County in the output data (Customer Details with Region)

...

• Press Image Removed

1. • 1. Press Image Added
   • Note that the pipe linking RegionsByCounty to Customer Details with Region

...

1. • is dashed; this indicates that this is a lookup pipe
   • When adding

...

1. • 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:

1. Hover over RegionsByCounty and in the hover menu

...

1. press Image Added Show Stream Attributes
2. The list of the

...

1. stream attributes will pop up
2. Drag the attribute Region from RegionsByCounty on top of Customer Details with Region
3. Run

...

1. analysis on Customer Details with Region
2. Have a look at the data in this

...

1. stream – check that the Region

...

1. attribute has been populated with the value looked up from RegionsByCounty