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 recordspass only some records to the receiving stream using the Maximum Number of Records per Group option.

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

In PhixFlow, we use an aggregate stream to show that we are rearranging data from a single stream. See the reference page Aggregate Stream for details.

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.
   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.
   4. PhixFlow opens the pipe settings. Click 

      Insert excerpt
      _finish _finish nopanel true

       to close the settings.
4. Drag all attributes from Combined Updates to Latest Package Update.
   1. Make sure the open settings form is for the stream Latest Package Update.
   2. Hover your mouse pointer over the stream Combined Updates again.
   3. In the context toolbar, click 

      Insert excerpt
      _stream_attributes _stream_attributes nopanel true

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

      Insert excerpt
      _finish _finish nopanel true

       to save and close the Latest Package Updates settings.
   6. Close the attributes pop-up window.
5. 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 all the data from the Combined Updates stream.

Group and order data on an input pipe

At  At the moment the data will come into the aggregate stream in any order. You are going to In PhixFlow you can sort the data as it comes through the input pipe to the aggregate stream. You can also choose to add specific records to the receiving stream.

Group and order data on an input pipe

In PhixFlow, as data records are passed through the model, you can rearrange the records or select records that meet certain criteria. You can do this using options on the stream or options on the pipe. In this section you will use options on the pipe to pass one record per customer to the receiving stream. That record will be the most recent one for the customer. To achieve this you will:

• group the data, so that all the records with the same Customer Ref number are grouped into adjacent rows in the table.
• Sort sort the data by date, from the newest to oldest, using the oldest Sales Date. This means that the first record delivered by the input pipe, for each Customer Ref, is the most recent for that customer.
• pass only the first record per customer record to the receiving stream.

1. Double In the model, double-click on the pipe from the  Combined Updates stream.
2. Expand the Sort/Group section toolbar.
3. In the section toolbar, click

   Insert excerpt
   _stream_attributes _stream_attributes nopanel true

    (check  - Show Stream Attributes)?? Phixflow opens a list of the attributes - how does it know these?that the Combined Updates stream can pass to the pipe.
   
4. Drag in CustomerRef. By default, it is set to have this attribute has Group ticked and Direction set to A-Z (ascending).
   This means the data records will be grouped by CustomerRef.
5. Drag in SalesDate. To make this an ordering attribute: Double-click on SalesDate in the Sort/Group gridSet the Sort Direction to descending: . PhixFlow opens a settings form for the attribute. In Basic Settings, set:
   • Direction: Z-A (descending)
     
   Untick the Group flag.
   • Group: 

     Insert excerpt
     _check_box_untick _check_box_untick nopanel true

   • Click 

     Insert excerpt
     _finish _finish nopanel true

      to save the change and close the attribute settings.
     This means the data records will be sorted by date, with the most recent first.
Set
6. •  
7. In the pipe settings → Sort/Group, set Maximum Number of Records per Group to 1.
   This means PhixFlow will pass only one record, the first and therefore most recent, to the receiving stream.
   
   
8. Click 

   Insert excerpt
   _finishsave _finishsave nopanel true

    to save and close the pipe settings., which should look like this:
   

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

This is effectively filtering out old records for the same customer.

Test your new Stream

...

1. In the model toolbar, click 

   Insert excerpt
   _save _save nopanel true

    to save the model.

Now you can run the model and view the stream data.

1. Hover your mouse pointer over the Latest Package Update stream and click 

   Insert excerpt
   _run_analysis _run_analysis nopanel true

    and click YES to confirm.
2. When PhixFlow notifies you that the analysis is finished, hover your mouse pointer over the Latest Package Update stream again.
3. Click 

   Insert excerpt
   _show_stream_views _show_stream_views nopanel true

   .
4. In the drop-down list, select 

   Insert excerpt
   _default_view _default_view nopanel true

   .  PhixFlow displays the data.
   Image Added
5. Notice that there is only one record per customer reference.
6. If you also look at the default view for the Combined Updates stream, you will see that some customers, such as C056 have more than one record, and the most recent record has been passed to the Latest package Update stream.