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

• Create an Aggregate Streamaggregate stream
• Set Ordering ordering on pipes
• Use Maximum Number of Records per Group on a pipe

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

• Find the icon Image Removed icon Image Added - Add New Stream (Aggregate), and drag this onto the modelling pane
• Give this Stream the Name: Latest Package Update
• Add a pipe from Combined Updates:
  • Hover over Combined Updates and press Image Modified- Add a new Connector
  • Click on Latest Package Update to connect the pipeCall the pipe in
• Drag all attributes from Combined Updates to Latest Package Update

...

...

Set up Grouping and Ordering on input pipe

You will set up the grouping needed (by Customer Ref) on the input pipe to the Aggregate Stream 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. This means that the first record delivered by the input pipe, for each Customer Ref, is the most recent for that customer.

• Double - click on the pipe from Combined Updates
• Go to the Sort/Group tab section
  • Press Press Image Modified - Show the list of Stream Attributes
    
  • Drag in CustomerRef
  • Drag in SalesDate
  • You will leave CustomerRef as a grouping attribute
  • You will update SalesDate to be a ordering attribute:
    • Double - click on SalesDate in the Sort/Group attributes list
    • Set the Sort Direction to Descendingdescending: Z-A
      
    • Untick the Group flag
    Press
    • Press Image Modified
  • 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

• Press Image Removed Press Image Added in the main pipe configuration form to save your changes

...

Test your new Stream

• Run analysis on your new Streamstream
• Make sure that for all customers the most recent sales update has been selected