Creates an aggregated dynamic column. Aggregate enables you to group information from different tables. For example, you can group information about orders in a table that contains customer data 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
Average value
Count
Record count
First
Minimum value of an alphabetically sorted range of records
Last
Maximum value of an alphabetically sorted range of records
Sum
Sum of the column value in all records
Mean (integer)
Mean value in Integer format
Mean (real)
Mean values in Real format
Minimum value
Minimum value of the selected range of records
Maximum value
Maximum value of the selected range of records
Standard deviation
Value indicating the average distance from the average
Square sum
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 the Transform tab, 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 the list of transformation commands, as shown in Figure 5‑10.
Figure 5‑10 Creating a transformation instruction
10 Choose “Save” to save the instruction in the main file.
