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

...

In this exercise you will repeat the comparison you did in the previous exercise, of the full channel list in your model against the list in the inventory database – but this time using a Mergemerge:

• Add a new Merge Stream merge stream to your model, by dragging the icon Image Removed icon Image Added 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 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 CenterView's automated merge builder – therefore, if possible, always load data from external tables into CenterView 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
      • section
      Press
      • Press Image Modified and drag in CustomerRef and Channel
      Press
      • Press Image Modified to save your changes
    • Double-click on the pipe inv
      • Go to the Sort/Group
      tab
      • section
      Press
      • Press Image Modified
      • In the new
      Grouping Attribute
      • grouping attribute form that pops up:
        • Select the attribute CustomerRef from the drop down list
        • Tick the Group flag
        Press Image Removed
        • Press Image Added
      • Add the grouping attribute Channel in the same way
      • Back in the main pipe configuration form,
      press Image Removed
      • press Image Added

Now you will complete the configuration of the Merge Streammerge stream, using the _key internal variable to write key values into the Streamstream:

• Double - click on Channel List Reconciliation 2 to open the configuration form
• Double - click the Attribute attribute CustomerRef
  • In the Attribute attribute details form that pops up, update Expression to: _key[1]
    • This writes the first key value into this attribute

...

• • Press Image Removed
• Double-click the Attribute
  • Press Image Added
  • Double click the attribute Channel
  • In the Attribute attribute details form that pops up, update Expression to: _key[2]
    • This writes the second key value into this attribute
  • Press Image RemovedPress Image Added
  • Add an
  Attribute
  • attribute SalesCheck, and set the Expression to

if (countElements(sales) == 1,

...

 "YES"

...

 ,

...

 "NO"

...

 )

• Add an Attribute attribute InvCheck, and set the Expression to

if (countElements(inv) == 1,

...

 "YES"

...

 ,

...

 "NO"

...

 )

• Press Press Image Modified in the main stream configuration form to save your changes
• Update the query in the Database Collector database collector to:

select * from SOURCE_ALL_CHANNELS_LIST

...

 order by CustomerRef, Channel

...

 (the ordering is needed to support the merge)

• 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

This is because a Merge merge will generate an output record for every key value it finds across all input pipes, whereas if you do a data enrichment using a lookup pipe – you will only get an output record for every record from the input pipe

...

...

Filter unmatched rows

To help you find unmatched rows in your output data, you will now add a filter (if you have already done the User course or Dashboards and Forms course, this should be familiar):

• Open the data set you have just generated in Channel List Reconciliation 2
• Press Image Removed - Select, create or clear a filter
• Press Image Removed 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 Create a filter:

• 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