Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

By the end of this chapter you will be able to:

...

  • Add a new Merge Stream to your model, by dragging the icon onto your model pane
  • Call this stream Name Channel List Reconciliation 2
  • Add an input pipe from Latest Package All Channels, with Name sales
  • Drag the following attributes from Latest Package All Channels to Channel List Reconciliation 2:
    • CustomerRef
    • Channel
  • Add an pipe from the Database Collector SOURCE_ALL_CHANNELS_LIST to Channel List Reconciliation 2
    • Call this pipe inv

If you set up a merge directly from an external database table you cannot use PhixFlow's automated merge builder – therefore, if possible, always load data from external tables into PhixFlow first, and then merge
It is useful to cover the technique of direct merges, however, since sometimes you need to merge from very large external tables

  • Set up the grouping attributes on the input pipes:
    • Double click on the pipe sales
      • Go to the Sort/Group tab
      • Press  and drag in CustomerRef and Channel
      • Press  to save your changes
    • Double-click on the pipe inv
      • Go to the Sort/Group tab
      • Press 
      • In the new Grouping Attribute form that pops up:
        • Select the attribute CustomerRef from the drop down list
        • Tick the Group flag
        • Press 
      • Add the grouping attribute Channel in the same way
      • Back in the main pipe configuration form, press 

...

  • Double-click on Channel List Reconciliation 2 to open the configuration form
  • Double-click the Attribute CustomerRef
    • In the Attribute details form that pops up, update Expression to: _key[1]
      • This writes the first key value into this attribute – if you have a look at one of the merges you created with PhixFlow's automated merge builder, you will see an expression like this for the grouping attributes
    • Press 
    • Double-click the Attribute Channel
    • In the Attribute details form that pops up, update Expression to: _key[2]
      • This writes the second key value into this attribute
    • Press 
    • Add an Attribute SalesCheck, and set the Expression to
Code Block
if (countElements(sales) == 1,

...


 "YES"

...


 ,

...


 "NO"

...


 )
  • Add an Attribute InvCheck, and set the Expression to
Code Block
if (countElements(inv) == 1,

...


 "YES"

...


 ,

...


 "NO"

...


 )
  • Press  in the main stream configuration form to save your changes
  • Update the query in the Database Collector to:
Code Block
select * from SOURCE_ALL_CHANNELS_LIST

...


 order by CustomerRef, Channel

...


 (the ordering is needed to support the merge)

When merging directly from database tables –you have to repeat the ordering of data across the query and the pipe; that is, the ordering of data in the Database Collector query much match the grouping on the pipe from the Database Collector

  • Run Analysis on Channel List Reconciliation 2
  • In your results you should see an additional 3 records that were not included in the reconciliation you did in the previous exercise – channels that are recorded in the inventory, but not in the channels list, are included in your output

...

  • Open the data set you have just generated in Channel List Reconciliation 2
  • Press - Select, create or clear a filter
  • Press  in the drop-down list that appears
  • In the filter details form, press the condition at the top of conditions list that says Where ALL of the following are true
    • From the drop down list select ANY item true
    • The top condition will now say Where ANY of the following are true
  • In the second condition in the conditions list:
    • Select SalesCheck
    • Leave the operator as equals
    • In the value field enter: NO
  • Add a third condition:
    • Select InvCheck
    • Leave the operator as equals
    • In the value field enter: NO
  • Press - Apply the filter without saving
  • You will get a list of all non-matched records, that is, all records where the SalesCheck value is NO or the InvCheck value is NO