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 table 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:

1. Open the model Channel Package Check
2. Add a new table to your model from Latest Package All Channels, called Channel List Reconciliation
3. Drag all the attributes from Latest Package All Channels into Channel List Reconciliation
4. Create a database collector, using the datasource CRM, to read all records from the table SOURCE_ALL_CHANNELS_LIST
5. Add a lookup pipe from the database collector SOURCE_ALL_CHANNELS_LIST to the table Channel List Reconciliation
   1. Call the pipe inv
   2. Go to the Order/Index section
   3. Press  Show Attributes
   4. Drag the attribute CustomerRef from the left-hand box into the list of Order/Index attributes.
   5. Drag the attribute CustomerRef from the right-hand box into the list of Order/Index attributes again – on top of the matching attribute CustomerRef you created above – this will populate the right-hand part of the matching attribute.
   6. Drag the attribute Channel from the left-hand box into the list of Order/Index attributes.
   7. Drag the attribute Channel from the right-hand box into the list of Order/Index attributes - on top of the matching attribute Channel you created above.

This lookup will find values in the external database table which match the output records on CustomerRef and Channel:

1. Press  OK in the main pipe configuration form to save your changes
2. Open the configuration form for the table Channel List Reconciliation
3. Add an attribute with Name InvoiceCheck.
4. 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:

   1. Set the Expression of the attribute to: 

      if (countElements(inv) == 1,
       "YES"
       ,
       // ELSE	  
       "NO"
      )

5. Save your changes to the table.
6. Run analysis on Channel List Reconciliation
7. Review the results, and see which channels reconciled against the list from the inventory database, and which did not.
   1. Remember that you can open the data in the external inventory table using the PhixFlow schema browser – if you want to verify the results.