PhixFlow Help

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 3 Current »

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
  • No labels