PhixFlow Help
7 Enriching data directly from database tables
By the end of this chapter you will be able to:
- Enrich a data set using a lookup pipe directly from a database collector
- Use the attribute function countElements()
In this exercise you will compare the stream you created in the previous exercise, listing all channels for each customer, with a list of assigned channels stored in an inventory database. To do this, you will create a lookup directly from a database collector:
- Open the model
Channel Package Check
- Add a new stream to your model from
Latest Package All Channels
, calledChannel List Reconciliation
- Drag all the attributes from
Latest Package All Channels
intoChannel List Reconciliation
- Create a database collector, using the datasource
Inventory DB
, to read all records from the tableSOURCE_ALL_CHANNELS_LIST
- Add a lookup pipe from the database collector
SOURCE_ALL_CHANNELS_LIST
to the streamChannel List Reconciliation
- Call the pipe
inv
- Go to the Order/Index section
- Press
- Drag the attribute
CustomerRef
from the right hand box into the list of matching attributes - Drag the attribute
CustomerRef
from the right hand box into the list of matching attributes again – on top of the matching attributeCustomerRef
you created above – this will populate the right hand part of the matching attribute - Drag
Channel
from the right hand box into the list of matching attributes - Drag
Channel
from the right hand box into the list of matching attributes again - on top of the matching attribute forChannel
that you created above
- Call the pipe
This lookup will find values in the external database table which match the output records on CustomerRef
and Channel
- Press in the main pipe configuration form to save your changes
- Open the configuration form for the stream
Channel List Reconciliation
- Add an attribute with Name InvoiceCheck
- In this attribute, you will record whether you returned the channel from the inventory table - to do this, you will count the records returned from the lookup; if this is 1, you will record that the reconciliation check was successful:
- Set the Expression of the Attribute to:
if (countElements(inv) == 1, "YES" , "NO" )
- Save your changes to the stream
- Run analysis on
Channel List Reconciliation
- Review the results, and see which channels reconciled against the list from the inventory database, and which did not
- Remember that you can open the data in the external inventory table using the PhixFlow schema browser – if you want to verify the results
Please let us know if we could improve this page feedback@phixflow.com