Creates an aggregated dynamic column. Aggregate allows you to group information from different tables. For example, you can group information about orders in a table that contains customer data in order to determine how many orders a customer has placed. Aggregation works with any table in the database as long as the tables are properly joined.
An Aggregate instruction must have the following syntax:
Defines the source column for the aggregation. The full definition includes, the database name, the table name, and the column name.
Function
Defines the aggregation function. The supported functions are shown in Table 5‑2.
Table 5‑2 Aggregation functions
Function
Returns
Average
The average value
Count
The record count
First
The minimum value of an alphabetically sorted range of records
Last
The maximum value of an alphabetically sorted range of records
Sum
The sum of the column value in all records
Mean (integer)
The mean value in Integer format
Mean (real)
The mean values in Real format
Minimum value
The minimum value of the selected range of records
Maximum value
The maximum value of the selected range of records
Standard deviation
A value indicating the average distance from the average
Square sum
The sum of squares of the distance between each data point and the line of best fit
Filter
Use Domain definition to define a filter.
Description
Type text that describes the new column. You can type the text in the Description text box, or double-click to open a text editor and type the text there.
How to create an aggregating column
1 In Main File, choose New. The list of transformation instructions appears.
2 In the list of transformation instructions, select AGGREGATE. Aggregate appears as shown in Figure 5‑9.
Figure 5‑9 Creating an aggregating column
3 In Table, select the target database and table from the drop-down lists.
4 In Column, type the name of the target column.
5 In Source column, select the database, the table, and the column you want to use to create the new column.
6 In Function, select a function from the drop-down list.
7 In Filter, choose Click to select define a domain, to define a filter. Domain definition appears. For more details about defining a domain see DOMAIN, later in this section.
8 In Description, type text that describes the new column.
9 Choose OK. The aggregate instruction appears in Main file, as shown in Figure 5‑10.
Figure 5‑10 Creating a transformation instruction
10 Choose Save to save the instruction in the main file.
