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
    How?

   1. Click Datasource in the toolbar to show the list of available Datasources in the Repository
   2. Drag the Datasource CRM onto the model
   3. Hover over the CRM Datasource and click  Database Collector in the pop up menu
   4. Name the new Database Collector, 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. Change the Type to Look-up
   3. Then  Applythe pipe Properties 
   4. Go to the Order/Index section
   5. Press  Show Attributes
   6. 
   7. Drag the attribute CustomerRef from the left-hand box into the list of Order/Index attributes.
   8. 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.
   9. Drag the attribute Channel from the left-hand box into the list of Order/Index attributes.
   10. 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  Apply and Close in the pipe Properties to save your changes
2. Click on the table Channel List Reconciliation to open the Properties
3. Add an attribute with Name InvoiceCheck.
4. In this attribute, you will record whether you returned the channel from the database. 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 database, and which did not.
   1. You can open the data in the database by hovering over the datasource and selecting  Show Tables to verify the results.
8. In the model window, click  Save.