Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 11 Next »


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:

  • Open the model Channel Package Check
  • Add a new table 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 the table Channel List Reconciliation
    • Call the pipe inv
    • Go to the Order/Index section
    • Press  Show Attributes
    • Drag the attribute CustomerRef from the left hand box into the list of Order/Index attributes.
    • 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.
    • Drag the attribute Channel from the left hand box into the list of Order/Index attributes.
    • 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:

  • Press  OK in the main pipe configuration form to save your changes
  • Open the configuration form for the table 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"
 ,
 // ELSE	  
 "NO"
)
  • Save your changes to the table.
  • 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.


  • No labels