Transformation Guide

Back Next

Store Values Across Rows

You can configure variables in transformations to store data from source rows. You can use the variables in transformation expressions.

For example, a source file contains the following rows:

California
California
California
Hawaii    
Hawaii    
New Mexico
New Mexico
New Mexico

Each row contains a state. You need to count the number of rows and return the row count for each state:

California,3
Hawaii    ,2
New Mexico,3

You can configure an Aggregator transformation to group the source rows by state and count the number of rows in each group. Configure a variable in the Aggregator transformation to store the row count. Define another variable to store the state name from the previous row.

The Aggregator transformation has the following ports:

Port	Port Type	Expression	Description
State	Pass-through	n/a	The name of a state. The source rows are grouped by the state name. The Aggregator transformation returns one row for each state.
State_Count	Variable	IIF (PREVIOUS_STATE = STATE, STATE_COUNT +1, 1)	The row count for the current State. When the value of the current State column is the same as the Previous_State column, the Integration Service increments State_Count. Otherwise, it resets the State_Count to 1.
Previous_State	Variable	State	The value of the State column in the previous row. When the Integration Service processes a row, it moves the State value to Previous_State.
State_Counter	Output	State_Count	The number of rows the Aggregator transformation processed for a state. The Integration Service returns State_Counter once for each state.

Rename Saved Search

Table of Contents

Transformation Guide

Transformation Guide

Store Values Across Rows

Store Values Across Rows