Versions Compared
compared with
Key
- This line was added.
- This line was removed.
- Formatting was changed.
Insert excerpt | ||||||||
---|---|---|---|---|---|---|---|---|
|
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:
- Hover over the
...
- table
Customer All Details
, and
...
- press
.Insert excerpt _table_new _table_new nopanel true - Call this
...
- table:
Customer Details with Region.
- Copy all attributes into your new
...
- table:
- Hover over Customer All Details and in the hover menu
...
- press
Insert excerpt _table_attributes_show _table_attributes_show nopanel true - 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
- press
...
- table
Customer Details with Region.
- table
- Run your new
...
- table and look at the data in it to verify that you have copied data from the merge
...
- table.
- 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:
- Hover over
RegionsByCounty
and press
...
Insert excerpt _pipe _pipe nopanel true - 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.
- In the pipe details form that pops up, 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 we will set up the fields to match across the two
- Name:
...
- recordsets:
- Press
- Press
- recordsets:
...
.Insert excerpt _attributes_show _attributes_show nopanel true - Drag the attribute
County
from the left hand list (the input attributes from the
...
- table
RegionsByCounty
) into the list of Order/Index attributes. - Drag the attribute
County
from the right hand list (the output attributes from the
- table
...
- table
Customer Details with Region
) on top of the Order/Index attributeCounty
- table
...
.
- In the Order/Index section, you should see
...
- that the Index Expression
...
- column has updated to
_out.
- column has updated to
...
County
Image Added- This means that the lookup will match records in the reference data (
RegionsByCounty
) whereCounty
...
- equals
County
in the output data (Customer Details with Region
).
- equals
- Press
...
Insert excerpt _finish _finish nopanel true
- Note that the pipe linking
RegionsByCounty
toCustomer 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 toCustomer 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
Insert excerpt _table_attributes_show _table_attributes_show nopanel true - The list of the
...
- table attributes will pop up.
- Drag the attribute
Region
fromRegionsByCounty
on top ofCustomer Details with Region.
- Run analysis on
Customer Details with Region.
...
- View the data in
...
- the table. Check that the
Region
attribute has been populated with the value looked up fromRegionsByCounty.