Page Comparison

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 Inventory DB CRM, to read all records from the table SOURCE_ALL_CHANNELS_LIST

   
   

   Expand
   title How?
   Click Datasource in the toolbar to show the list of available Datasources in the Repository Drag the Datasource CRM onto the model Hover over the CRM Datasource and click  Insert excerpt _database_collector _database_collector nopanel true  in the pop up menu 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 

      Insert excerpt
      _save _save nopanel true

      the pipe Properties 
   4. Go to the Order/Index section
   5. Press

      Insert excerpt
      _attributes_show _attributes_show nopanel true

   6. Image Added
   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 

   Insert excerpt
   _finish _finish nopanel true

    in the main pipe configuration form Properties to save your changes
2. Open the configuration form for the 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 inventory table - to 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: 

      Code Block
      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 You can open the data in the external inventory table using the PhixFlow schema browser – if you want to verify the resultsdatabase by hovering over the datasource and selecting

      Insert excerpt
      _datasource_show_tables _datasource_show_tables nopanel true

       to verify the results.
8. In the model window, click 

   Insert excerpt
   _save_saveModel _save_saveModel nopanel true

   .