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

• Merge directly from database collectors

• Populate attributes with key values using the '_key[n]' notation

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

merge:

1. Add a new

1. merge table to your Channel Package Check model, by dragging

1. in

   Insert excerpt
   _table_merge _table_merge nopanel true

    from the analysis toolbar.
2. Name this table: Channel List Reconciliation 2
3. Add

1. a pipe from Latest Package All Channels, with the Name: sales
2. Drag the following attributes from Latest Package All Channels to Channel List Reconciliation 2:
   1. CustomerRef
   2. Channel
3. Add

1. a pipe from the

1. database collector SOURCE_ALL_CHANNELS_LIST to Channel List Reconciliation 2

1. 1. Name this pipe

1. 1. : inv

2. Set up the grouping attributes on the

1. pipe:

   1. Double click on the pipe sales
      1. Go to the Sort/Group

1. 1. 1. section.
      2. Press 

         Insert excerpt
         _attributes_show _attributes_show nopanel true

          and drag in the attributes CustomerRef and Channel.
      3. Press 

1. 1. 1. Insert excerpt
         _finish _finish nopanel true

          to save your changes.
   2. Double-click on the pipe inv
      1. Go to the Sort/Group

1. 1. 1. section.
      2. Press 

1. 1. 1. Insert excerpt
         _addIcon _addIcon nopanel true

         .
      2. In the new

1. 1. 1. grouping attribute form that pops up:
         1. Select the attribute CustomerRef from the drop down list.
         2. Tick the Group flag
         3. Press 

1. 1. 1. 1. Insert excerpt
            _finish _finish nopanel true

            .
      2. Add the grouping attribute Channel in the same way.
      3. Back in the

1. 1. 1. pipe

1. 1. 1. Properties, press 

1. 1. 1. Insert excerpt
         _finish _finish nopanel true

         .

Now you will complete the configuration of the

merge table, using the _key internal variable to write key values into the

table:

1. Double

1. click on Channel List Reconciliation 2 to open the

1. Properties.
2. Double

1. click the

1. attribute CustomerRef.
   1. In the

1. 1. attribute details form that pops up, update Expression to: _key[1]
      1. This writes the first key value into this attribute

1. 1. 1. .
   2. Press 

      Insert excerpt
      _finish _finish nopanel true

   3. Double click the attribute Channel
   4. In the

1. 1. attribute details form that pops up, update Expression to: _key[2]
      1. This writes the second key value into this attribute.
   2. Press 

1. 1. Insert excerpt
      _finish _finish nopanel true

      .
   2. Add an

1. 1. attribute SalesCheck, and set the Expression to

if (countElements(sales) == 1,
 "YES"
 ,
 "NO"
 )

3. Add an attribute InvCheck, and set the Expression to:

if (countElements(inv) == 1,
 "YES"
 ,
 "NO"
 )

4. Press 

5. Update

the Expression in the

SOURCE_ALL_CHANNELS_LIST database collector to:

select * from SOURCE_ALL_CHANNELS_LIST
 order by CustomerRef, Channel

6. Toggle 

6. Run Analysis on Channel List Reconciliation 2.

7. 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 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

:

1. Open the

1. recordset you have just generated in Channel List Reconciliation 2

1. by hovering over this table and pressing 

   Insert excerpt
   _recordsets_show _recordsets_show nopanel true

   .
2. Create a filter:
   
   1. Where ANY of the following are true
      

1. 1. 1. SalesCheck

1. 1. 1. equals

1. 1. 1. NO

1. 1. 1. InvCheck

1. 1. 1. equals

1. 1. 1. NO

1. Press Apply to apply the filter without saving.
2. 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.
3. In the model window, click 

   Insert excerpt
   _save_saveModel _save_saveModel nopanel true

   .