Aggregation - ordered list

The Ordered List aggregation is a variation of the existing List Distinct option. List Distinct returns a delimited list, ordered by the most frequently occurring values, together with the number of times each has occurred – see below:

Using ordered list aggregation¶

The Ordered List aggregation function puts all transactions into order by the selected sequence variable and returns the information as a delimited list. For example:

1. Create a new data grid and add the Surname, Region and Destination variables.

   By default, the data grid will set itself to the lowest table level of the variables in use – here Bookings filtered to Return all Bookings, but override this and:

2. Right-click and set the table level to People.

3. Click on the icon, check the Apply Column Aggregation box and set the Grouping table to People and the Transactional table to Bookings.

4. Click on the Edit Sequence Variable… button and drag Booking Date onto the Sequence by drop-box.

   By default, the List Distinct function is applied to the Destination column. To amend this:

5. Right click on the Destination column header and select Aggregate to People…

6. Select Ordered List from the Function drop-down menu.

   

7. Rebuild the display.

   

   To verify the results and see the date of each booking:

8. Add the Booking Date variable to the Data Grid.

   As above:

9. Change the default List Distinct to the Ordered List function and rebuild.

   

See also: Column Aggregation.