AGGREGATE
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:
Syntax
AGGREGATE [Target tab le][Target column][Source column][Function]([Filter])
Parameters
Target table
Defines the name of the target table, for example [SalesData].[MonthlySales]. The definition contains the database name, followed by the table name.
Target column
Defines the name of the target column. Do not use the following characters:
/ \ º ª - accents, dieresis,?, !, *, @, #, &, $, o, ñ
Source column
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‑8.
Figure 5‑8 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 how to define 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‑9.
Figure 5‑9 Creating a transformation instruction
10 Choose Save to save the instruction in the main file.