Sequence Number Generation

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  PayPal

IMPORTANT,  YOU are responsible for backing up your systems before trying or implementing any suggestions from this blog. I do not guarantee 100% accuracy of any code examples. I do not presume to know your system environment(s) or Security requirements; all code examples from this blog should be thoroughly tested before any attempted use on a production system.

0 Response to "Sequence Number Generation"

Post a Comment