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.
Reset Sequence number
Moderators: chulett, rschirm, roy
Reset Sequence number
CHeers
Baksh
Baksh
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
Reset Sequence number
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.
as kandyshandy said use stage variables.
-
- Participant
- Posts: 437
- Joined: Fri Oct 21, 2005 10:00 pm
Re: Reset Sequence number
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
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
Keith Williams
keith@peacefieldinc.com
keith@peacefieldinc.com