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:

Add a new Stream to your model from Latest Package All Channels, called Channel List Reconciliation
Drag all the attributes from Latest Package All Channels into Channel List Reconciliation
Create a Database Collector, using the Datasource Inventory DB, to read all records from the table SOURCE_ALL_CHANNELS_LIST
Add a lookup pipe from the Database Collector SOURCE_ALL_CHANNELS_LIST to Channel List Reconciliation
- Call the pipe inv
- In the matching attributes form create a match:
  - 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 attribute CustomerRef you created above – this will populate the right hand part of the matching attribute
- Create another match
  - 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 for Channel that you created above

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