Aggregation wizard

The Aggregation wizard creates a virtual variable from an RFV Value Selection. This will create a variable on the RFV Grouping Table that will contain a value calculated from records on the Transaction Table associated with each Grouping record that meet the selection criteria, using the specified function.

For example, suppose you have an RFV selection with the Grouping Table of People and Transaction Table of Bookings and the Value element of the RFV is the average (mean) of Booking Cost where it is greater than 100.

If this selection is used in the Aggregation Wizard you will create a variable on the Grouping Table (People) that contains the average (mean) Booking cost of all bookings that cost over 100 pounds. Any People that don't have any Bookings associated with them that cost over 100 pounds will have no value associated with them for this variable (the value will be "missing").

The Aggregation wizard creates a virtual variable on a specified table in the system (identified as the grouping table) that contains a value calculated (such as a sum or average) from records on a child table (the transaction table) that are associated with each grouping record.

The grouping table and transaction table can be specified in the wizard or by dragging on a selection with a RFV Value.

If an RFV Value selection is used, the settings specified in the RFV Value definition will also be used. For example, suppose you have an RFV selection with the grouping table of People and transaction table of Bookings and the value settings of the RFV are set to the average (mean) of Booking cost of all bookings that cost over 100 pounds. Using this in the wizard will create a variable on the grouping table (People) that contains the average cost of all Bookings associated with that Person that cost more than 100 pounds.

On the Wizards tab click on the link to start the wizard - Start

Start¶

This is the first page of the Aggregation wizard and lets you choose how to define the variable to be created.

If you have an RFV Value selection that you want to use to set how the variable will be defined, then choose the Yes radio button and you will be asked to specify the selection. If you don't, then choosing the No radio button will allow you to define the grouping and transaction tables.

If you select:

Yes, I have an RFV selection — click Next to go to Source
No, I don't have an RFV selection — click Next to go to Transaction selection

Source¶

If you have an RFV Value selection to use, you should specify it here. If there is more than one RFV element in the selection, the next step of the wizard

Any selection elements "above" the RFV element will select which grouping records to set the virtual variable for. Any grouping records that fall outside of this selection will have no value set.

Drag on the selection containing the RFV node(s)

If there is only one RFV Value node, click Next to go to Output type
If there is more than one RFV Value node, click Next to go to RFV selection

RFV selection¶

If the selection that was specified in the previous step had more than one RFV element in it, you will have to specify which RFV element to use

Drag the appropriate RFV Value node on to the drop box.

Transaction selection¶

If you don't have a RFV Value selection then you need to specify a grouping and transaction table. The transaction table is the first one to be specified and is done so by specifying the group of transaction records to include.

For example, a grouping table could be People and a transaction table could be Bookings. Assuming of Booking Cost, the variable created for each Person by the wizard will be the sum of the cost of all the Bookings taken by that person.

If, however, you want to find the sum of the cost of all flight-only Bookings, drop a selection of Bookings that are flight-only here.

Grouping table¶

Once a transaction table has been chosen, the grouping table can then be specified.

The grouping table must be a parent or "ancestor" table of the transaction table (i.e. it must be higher up the System Tables diagram than the transaction table.

For example, if the Bookings table was specified as the transaction table, the grouping table could either be People or Households given the following table structure:

Value¶

Once the transaction and grouping tables have been specified, you need to define how to aggregate the transaction records for each grouping record.

A numeric variable from the transaction table must be specified by dragging on to the Drop value variable here drop box.

The Action can be either sum, average, minimum, maximum or distinct.

Output type¶

Regardless of whether an RFV selection was used at the beginning or whether transaction and grouping tables were specified explicitly, you can then specify if you want the results of the wizard to be returned as a banded Selector variable or an actual Numeric variable.

If you select Banded Output and predefined your RFV selection, click Next to go to Folder
If you select Banded Output and did not predefine your RFV selection, click Next to go to Banding
If you select Output as Actual Values, click Next to go to Precision

Precision¶

If you specified to output an Actual Value in the previous step then you can choose the number of decimal places that the variable will have.

Enter the number of Decimal Places.

Banding¶

If the transaction and grouping tables were defined explicitly and the output type was specified as Banded Output, this step of the wizard allows you to define what bandings you want.

Each band should be defined on a separate line and be in one of the following forms:

x The band will contain only the number x
x - y Equivalent to (>=x - <=y), the band will contain all numbers between x and y (including x and y)
x - <y The band will contain all numbers between x and y (not including y)
>x - y The band will contain all numbers between x and y (not including x)
>x The band will contain all numbers greater than (but not including) x
<y The band will contain all numbers less than (but not including) y

Enter the band definitions.

Folder¶

Choose the folder that the new variable should be created in.

Create (if necessary) and highlight the folder to hold your Virtual Variable.

Add notes¶

You can choose to add notes to your variable. These can be viewed later by right-clicking on the variable once it is in the System variables explorer window.

Enter any notes you wish to record.

Name¶

This step of the wizard allows you to define what the Virtual Variable will be called.

Enter the name of your variable in the Description box.

To overwrite an existing virtual variable drag it on to the Drop the variable to overwrite here drop box.

Tick the Create URN Snapshot box to record the exact records found at this time so it can be recreated after a refresh of the data if needed.

Tick the Modify Security Attributes box to allow you access to Security (This option is only available on the Enterprise Version)

Security¶

This is an optional step available on the Enterprise version of the software.

Use the tick boxes to set the security setting you wish to apply to this variable.

Finish¶

This step is shown after the actions required by this wizard have been completed. The new variable has been created and highlighted in the System Explorer.

Tick the box Show new variable as a selection if you want to view the variable when the wizard is closed.

Click Finish to close the wizard.