Informatica Common Components aggregator one aggregation expression

Conversion type: Connected, active
The aggregation transformation allows you to perform aggregate calculations, such as averages and sums. Aggregation conversions are different from expression conversions, and you can use aggregation transformations to perform calculations on multiple groups. Expression conversions allow you to perform calculations on a line-by-row basis.
When you use a conversion language to create an aggregation transformation, you can filter rows by using conditional clauses, which is more flexible than the SQL language.
PowerCenter performs its read aggregation calculations and stores the required data groups and row data in the aggregation cache.
After you create a session that contains an aggregation transformation, you can enable session options and incremental aggregations. When PowerCenter Server performs an incremental aggregation, it passes the new source data by mapping and uses the historical cache data to incrementally perform the new calculation.

The designer only allows aggregation expressions to appear in the aggregation transformation. An aggregation expression can include conditional clauses and non-aggregate functions. You can also include an aggregate function that embeds another aggregate function, such as:

MAX (COUNT ITEM)

The result of the aggregation expression varies depending on the port used in the conversion. For example, when PowerCenter calculates the following aggregate expression without a defined group by port, it will look for the total amount of items sold:

SUM (QUANTITY)

However, if you use the same expression and group by item port, PowerCenter returns the total amount of items sold by item.

You can create an aggregate expression on any output port and use multiple aggregation ports in the transformation.

Aggregation functions

You can use the following aggregate functions in the aggregation transformation. You can also embed an aggregate function into another aggregate function.

• Avg

• COUNT

• First

• Last

• MAX

• MEDIAN

• MIN

• Percentile

• STDDEV

• SUM

• VARIANCE

when you use any of these functions, you must use them in an expression of the aggregation transformation.

Nested aggregate functions

You can include multiple single-level or multiple nested functions in different output ports of the aggregation transformation. However, you cannot include both single-level and nested functions in the aggregation transformation. Therefore, if the aggregation transformation contains a single-level function in any output port, you will not be able to use nested functions on any other port in this conversion. When you include single-level and nested functions in the same aggregation transformation, the designer will mark the mapping or mapping component as invalid. If you need to create a single-level function and a nested function, set up an aggregate transformation, respectively.

Conditional clauses

You can use conditional clauses in an aggregate expression to reduce the number of rows used in the aggregation. A conditional clause can be any clause that evaluates to TRUE or FALSE.

For example, you can use the following expression to calculate the total commission for an employee who exceeds its quarter limit:

SUM (COMMISSION, COMMISSION > QUOTA)

Non-aggregate functions

You can use non-aggregate functions in an aggregate expression.

The following expression returns the highest number of items sold per item, grouped by project. If the item is not sold, the expression returns 0.

IIF (Max (QUANTITY) > 0, Max (QUANTITY), 0))

Null value in aggregate function

When configuring PowerCenter, you can choose how you want PowerCenter to handle the empty values of the aggregate function. You can choose to treat null values as null or zero in an aggregate function. By default, PowerCenter Server treats null values as NULL in aggregate functions

Informatica Common component aggregator aggregation expression