Jeff's Cluttered Mind

The Sequence Generator transformation is great sometimes but I cannot find a way to reset the generated sequence based on external conditions. For example, when a value in the current row is different from the value in a previous row, I would like to reset the Sequence number.

Using some information I found on Informatica’s Knowledge base, I created a reusable Expression that will increment or reset a counter (sequence number) based on an input port's value. The counter will increment when a chosen input port's current row is equal to the previous row and reset the counter when the current row's value does not match the value from a previous row.

How It Works

The expression saves a chosen port value into a variable port so it can be compared to subsequent rows, when the current port value matches the previous port value (saved in the variable), a sequence number is incremented. When the current and previous values do not match, the sequence number is reset to 1.

Using the Expression

Import the expression object (XML file) into a PowerCenter library, it will be stored in the "Transformations" sub-folder.

You can modify the port lengths as required.

The expression's input should be sorted by the input value.

Download the Expression here.

View The Code

Thank you for your support

or