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

...

You will also learn how to limit a pipe to read a maximum number of data records.

In this exercise you will select the latest (most recent) sales record for each customer, from the set of combined updates you created in the previous exercise.

Create an

...

aggregate stream

...

1. In the model toolbar, find 

   Insert excerpt
   _add_stream_aggregate _add_stream_aggregate nopanel true

     and drag it into the model.
2. Set the stream Name to Latest Package Update.
3. Add a pipe from Combined Updates:
   1. Hover your mouse pointer over the stream Combined Updates

...

1. 1. .
   2. In the context toolbar, click 

      Insert excerpt
      _add_pipe _add_pipe nopanel true

      .
   3. Click the stream Latest Package Update to connect the pipe.
2. Drag all attributes from Combined Updates to Latest Package Update

...

Set up Grouping and Ordering on input pipe

...

1. .
   1. Hover your mouse pointer over the stream Combined Updates again.
   2. In the context toolbar, click 

      Insert excerpt
      _stream_attributes _stream_attributes nopanel true

      . Phixflow lists the data attributes (column titles) for this stream.
   3. Select all the attributes and drag them into the Latest Package Update settings → Aggregate Attributes section.
      Image Added
   4. Click 

      Insert excerpt
      _finish _finish nopanel true

       to save and close the Latest Package Updates settings.
   5. Close the attributes pop-up window.
2. In the Channel Package model toolbar, click 

   Insert excerpt
   _save _save nopanel true

   .

When you next run this model, the Latest Package Updates stream will pull in the all the data from the Combined Updates stream.

Group and order data on an input pipe

At the moment the data will come into the aggregate stream in any order. You are going to sort the data as it comes through the input pipe to the aggregate stream – but also set up ordering of the data. Both of these are done on the pipe.

In this case you want to select, for each Customer Ref, the latest (most recent) package update.

So, in addition to grouping by Customer Ref, you will order by Sales Date descending.

• Group the data, so that all the records with the same Customer Ref number are adjacent in the table.
• Sort the data by date, newest to oldest, using Sales Date. This means that the first record delivered by the input pipe, for each Customer Ref, is the most recent for that customer.

1. Double click on the pipe from Combined Updates
2. Go to the Sort/Group section
   1. Press Image Modified - Show Stream Attributes
      
   2. Drag in CustomerRef
   3. Drag in SalesDate
   4. You will leave CustomerRef as a grouping attribute
   5. You will update SalesDate to be a ordering attribute:
      1. Double click on SalesDate in the Sort/Group attributes list
      2. Set the Sort Direction to descending: Z-A
         
      3. Untick the Group flag
      4. Press Image Modified
   6. Set Maximum Number of Records per Group to: 1

This setting means that for each key value – in this case for each value of CustomerRef – only the first record will be returned; since you have also ordered by SalesDate descending, this will be the latest (most recent) record for each CustomerRef

...