10 Aggregating in pipes
- Chris Welford
- Anthony George
- Fiona Sargeant (Unlicensed)
Owned by Chris Welford
By the end of this chapter you will be able to:
- Set up aggregate attributes on pipes
- Read aggregate attributes from pipes into a table
In this exercise you will create a report of total and average last billed amounts, aggregated by Region:
In your model, add a new table:
- Hover over
Customers in England
, and press Create New Table. - Give the new table the Name:
Customer Summary by Region
- Double-click on the new pipe to open the details form.
- Go to the Sort/Group section:
- Open the list of attributes, and drag the attribute
Region
into the list of Sort/Group attributes.
- Open the list of attributes, and drag the attribute
- Go to the Aggregate Attributes section:
- Press Add New to add a new aggregate attribute
- In the new aggregate attribute form:
- Set the Aggregate Function to Count
- Leave the Attribute blank
- Set the New Attribute Name to
CountRecords
- Press OK.
- Add another aggregate attribute:
- Press Show Attributes.
- Drag the attribute
BILLAMOUNT
into the aggregate attributes list - Note that this already uses the attribute function Sum – leave this as it is.
- Update the name of the aggregate value:
- Double-click on the new aggregate attribute.
- Update the Name to
TotalLastBillAmount
- Press OK.
- Add another aggregate attribute:
- Press Show attributes for <table>
- Drag the attribute
BILLAMOUNT
into the aggregate attributes list - Update the details for this new aggregate attribute:
- Double-click on it
- Update the Aggregate Function to Average.
- Update the Name to
AvgLastBillAmount
- Press OK.
- Press Show attributes for <table>
Now you need to configure your output table:
- Drag the attribute
Region
from the Sort/Group section of the pipe onto the tableCustomer Summary by Region.
- Drag the aggregate attributes you added from the Aggregate Attributes section of the pipe onto the table
Customer Summary by Region.
- In the main pipe configuration form, press OK.
Remember to save your model layout.
- Run analysis on
Customer Summary by Region
- View the data in this table – check that the aggregated report has been generated correctly.