4 Picking latest sales update for each customer
- Chris Welford
- Zoe Baldwin
- Anthony George
- Fiona Sargeant (Unlicensed)
By the end of this chapter you will be able to:
You will also learn how to pass only some records to the receiving table using the Maximum Number of Records per Group option.
In this exercise you will select the most recent sales record for each customer from the set of combined updates you created in the previous exercise.
Create an aggregate table
In PhixFlow, we use an aggregate table to show that we are rearranging data from a single table. See the reference page Aggregating Data for details.
- From the model toolbar, drag Aggregate Table into the model.
- Set the table Name to
Latest Package Update
. Add a pipe from the table
Combined Updates
to the tableLatest Package Update
How?- Hover your mouse pointer over the table
Combined Updates.
- In the context toolbar, click Connector.
- Click the table
Latest Package Update
to connect the pipe. - PhixFlow opens the pipe settings. Click Apply and Close to close the settings.
- Hover your mouse pointer over the table
Drag all attributes from
Combined Updates
toLatest Package Update
.How?- Hover your mouse pointer over the table
Combined Updates
. - In the context toolbar, click Attributes. PhixFlow lists the data attributes (column titles) for this table.
- Select all the attributes and drag them into the
Latest Package Update table
.
- Hover your mouse pointer over the table
- In the
Channel Package
model toolbar, click Save.
When you next run this model, the Latest Package Updates
table will pull in all the data from the Combined Updates
table. At the moment the data will come into the aggregate table in any order. In PhixFlow you can sort the data as it comes through the input pipe to the aggregate table. You can also choose to add specific records to the receiving table.
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 table or options on the pipe. In this section you will use options on the pipe to pass one record per customer to the receiving table. 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 the data from the newest to 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 table.
- In the model, double-click on the pipe from the
Combined Updates
table to theLatest Package Update
table. - Expand the Sort/Group section toolbar.
- In the section toolbar, click Attributes. Phixflow opens a list of the attributes that the
Combined Updates
table can pass to the pipe. - Drag
CustomerRef
to the Sort/Group section. By default, this attribute has Group ticked and Direction set to A-Z (ascending).
This means the data records will be grouped byCustomerRef
. - Drag in
SalesDate
. Double-click onSalesDate
in the Sort/Group grid. PhixFlow opens the settings for the attribute. In Basic Settings, set:- Direction: Z-A (descending)
- Group: Untick
- Click Apply and Close to save the change and close the attribute settings.
This means the data records will be sorted by date, with the most recent first.
- Direction: Z-A (descending)
- In the pipe settings → Sort/Group section, 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 table. - Click Apply to save the pipe settings, which should look like this:
- In the model toolbar, click Save to save the model.
Now you can run the model and view the table data.
- Hover your mouse pointer over the
Latest Package Update
table and click Run Analysis then click YES to confirm. - When PhixFlow notifies you that the analysis is finished, hover your mouse pointer over the
Latest Package Update
table again. - Click Show view.
- In the drop-down list, select Default View. PhixFlow displays the data.
Notice that there is only one record per customer reference. - If you also look at the default view for the
Combined Updates
table, you will see that some customers, such as C056 have more than one record. The most recent record has been passed to theLatest package Update
table.