Reset Sequence number

[bakshdwh]

Hi
I have incoming data as

Account Customer
----------- -------------
1 A
1 B
1 C
1 D
2 E
2 F
5 W
5 V
5 L
5 I
5 P

I want the sequence to be generated as

Account Customer seq
----------- ------------- ----------
1 A 1
1 B 2
1 C 3
1 D 4
2 E 1
2 F 2
5 W 1
5 V 2
5 L 3
5 I 4
5 P 5

That means.. for every new account number, it has to reset back to 1 and then increment it by one for every similar account number.

Note : the data is not sorted in the above order

Could you'll please help me on this ..

Thanks in anticipation.

[kandyshandy]

Use stage variables.

[sivanagu]

I think sequence number will give increment number's to all records. but u need for every record u have to start from 1. if it one time load or incremental load...

as kandyshandy said use stage variables.

[kwwilliams]

There are two options:

1. Use stage variables as the previous posters suggested. Make sure you have sorted the data on Customer and Account, and that you have partitioned the data on Customer.

Your stage variables could look like this:

svCurrKey = Account | Customer
svSeq = If svCurrKey = svPrevKey then svSeq + 1 else 1
svPrevKey = Account | Customer

Because the stage variables are processed in order listed, this will work because the svPrevKey value hsa not been set.

or

2. Use a combination of the sort stage and a transformer. If you partition going into the sort stage on customer. Then sort the data on customer and account. And set the Create Key Change Column to True.

In a subsequent transformer you can have a stage variable:

svSeq = If KeyChange = 1 then 1 else svSeq + 1

[bakshdwh]

Thanks Keith and all

Looks like its working for sample data.. it should definately work in the real time...

Thanks again..