How to assign sequence numbers to sorted data ?

vivekreddy · Post by **vivekreddy** » Mon Mar 05, 2007 3:08 am

Hi.

I have this data which at present has sequence numbers which are not in order due to some data records failing to migrate to target due to validation failure, and later on passing through after being suitably corrected.
Now I first of all sort the data on the basis of a key and the timestamp of the creation of the data record to get a proper logical sequence.

Now, how can I assign the appropriate sequence numbers to each record, considering that for a given key, there can be multiple data, and I am grouping the data on the basis of the key value.

So if I have key value as A, and corresponding records as 4 in number, so they should be logically numbered as 1-4, and for another key value B with corresponding records as 5 in number, the logical numbering should be 1-5.
Considering that both groups of records are in the same dataset, can anyone suggest as to how I can achieve this logical numbering?

kumar_s · Post by **kumar_s** » Mon Mar 05, 2007 4:03 am

Hope you are using Sort stage for sorting the data in the order. User "Create Key Change column" option in the Sort stage which will enable you additional field.
This will assign 1 to each new group.
User Transformer stage to store the previous record in a stage variable and add 1 till you find the current record as 1.

kumar_s · Post by **kumar_s** » Mon Mar 05, 2007 4:04 am

Hope you are using Sort stage for sorting the data in the order. User "Create Key Change column" option in the Sort stage which will enable you additional field.
This will assign 1 to each new group.
User Transformer stage to store the previous record in a stage variable and add 1 till you find the current record as 1.

kumar_s · Post by **kumar_s** » Mon Mar 05, 2007 4:04 am

Hope you are using Sort stage for sorting the data in the order. User "Create Key Change column" option in the Sort stage which will enable you additional field.
This will assign 1 to each new group.
User Transformer stage to store the previous record in a stage variable and add 1 till you find the current record as 1.

vivekreddy · Post by **vivekreddy** » Mon Mar 05, 2007 4:09 am

I am sorry, but could you please elaborate as to how I may store a previous value in a stage variable, and then use it for comparison?

kumar_s · Post by **kumar_s** » Mon Mar 05, 2007 4:19 am

Use stage variable in Transformer,

Code: Select all

Intial Assignment  vPreKey = 1

Counter => If Input.ChangeKeyColumn = 0 Then PreKey + 1 Else 0
vPreKey = Counter

Assing Counter to a Field.

Oh sorry, just now I notices that the post has been repeated.

DSguru2B · Post by **DSguru2B** » Mon Mar 05, 2007 8:01 am

A simple search would have revealed the methodolgy. Group changes and key assingment has been discussed a number of times.

ray.wurlod · Post by **ray.wurlod** » Mon Mar 05, 2007 8:08 am

Too difficult.

Just add a Column Generator, operating sequentially, downstream of the Sort stage (which presumably was also operating sequentially, or use a Sort Merge collector if not).

Use a Cycle with an increment of 1.