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 |
- 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
- 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 the pipe sales
...
- 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
- In the Attribute details form that pops up, update Expression to: _key[1]
| 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
- 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 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