Mapping Specification Aggregators
Add an aggregator to a mapping specification to perform aggregate calculations on multiple rows of data. You can add multiple aggregate functions to the aggregator.
When you add an aggregator, you can perform aggregate calculations on groups of columns or all columns. When you group by columns, you can apply the aggregate conditions and rules to multiple columns. You can include filters, rules, conditional clauses, and nested expressions in an aggregator. You can also add different aggregators to multiple columns.
You can add, edit, or delete an aggregator. You can also preview data in an aggregator.
You can add the following types of aggregators:
- Simple aggregator
- Aggregates data by applying conditions and rules to multiple columns. You can add lookup, rule, and source columns to the conditions in a simple aggregator.
- Advanced aggregator
- Aggregates data by applying aggregate functions and expressions to multiple columns. You can add lookup, rule, and source columns to the expression in a condition in an advanced aggregator.
Aggregator Properties
Configure aggregator properties when you add or edit an aggregator.
You can configure the following aggregator properties:
- Functions
Configure the following properties for aggregator functions:
- - Function. Type of aggregate function.
- - Aggregate Source Name. Source, lookup, or rule name for the column on which to apply the aggregate function.
- - Aggregate Column Name. Source, lookup, or rule column name on which to apply the aggregate function.
- - Target Column Name. Column name in the mapping specification target that returns the results of the aggregate function.
- Conditions
- Use aggregator conditions to reduce the number of rows in the aggregator. Configure the source, lookup, rule column, operator, and value for an aggregator condition.
- Functions and Conditions
Configure the following properties for functions and conditions:
- - Expression. Expression in the aggregator that contains the aggregate functions and conditions.
- - Target Column Name. Name of the target column that contains the results of the aggregator.
Adding and Editing a Simple Aggregator
Add a simple aggregator to apply conditions and rules to aggregate data on columns. Edit a simple aggregator to modify the conditions, rules, and group by columns.
1. From the Actions menu, click Edit > Aggregator.
The Edit Mapping Specification wizard appears.
2. Choose to add or edit an aggregator.
- - To add an aggregator, click the New icon.
- - To edit an aggregator, click the Edit icon.
3. To add or edit a simple aggregator, click Simple.
4. On the Functions panel, click the Add Rule icon and select the Function, Aggregate Source Name, Aggregate Column Name, and Target Column Name columns.
5. On the Conditions panel, select a column, operator, and enter a value to create a condition.
6. Click Next.
7. Optionally, on the Columns panel, select the columns you want to group together to perform aggregate calculations on groups of columns.
To select multiple columns press the Shift or Ctrl key and click each column.
8. Click the arrow icon to add the columns to the Group by panel.
9. Optionally, on the Data Preview panel, click Show to preview the data that is grouped together based on matching values in the group by columns.
10. Click Finish and then click Save.
Adding and Editing an Advanced Aggregator
Add an advanced aggregator to apply conditions and expressions to aggregate data on columns. Edit an advanced aggregator to modify the conditions, expressions, and group by columns.
1. From the Actions menu, click Edit > Aggregator.
The Edit Mapping Specification wizard appears.
2. Choose to add or edit an aggregator.
- - To add an aggregator, click the New icon.
- - To edit an aggregator, click the Edit icon.
3. To add or edit an advanced editor, click Advanced.
4. On the Functions and Conditions panel, click the Add Condition icon.
5. Select a target column name.
6. On the Functions tab, select a function, click the arrow icon, and select a column to add the function to the Expression editor.
7. Optionally, on the Rules tab, select a column and datatype and click the arrow icon to add the rule column to the Expression editor.
8. Optionally, on the Lookup tab, select a column and datatype and click the arrow icon to add the Lookup column to the Expression editor.
9. Click OK and then click Next.
10. Optionally, on the Columns panel, select the columns you want to group together to perform aggregate calculations on groups of columns.
To select multiple columns press the Shift or Ctrl key and click each column.
11. Click the arrow icon to add the columns to the Group by panel.
12. Optionally, on the Data Preview panel, click Show to preview the data that is grouped together based on matching values in the group by columns.
13. Click Finish and then click Save.