DSXchange

I have requirement to generate the sequence number for the duplicate enteries.

For Example

ID,Seq_number
10,1
10,2
20,1
20,2
30,1
30,2
30,3
30,4

Any suggestion Appreciated

Thanks In Advance

Use a stage variable to count the number of successive duplicate entries

"svSeqNumber" derivation : "if In.ID = svLastID THEN svSeqNumber+1 ELSE 1"
"svLastID" derivation "In.ID"

The order of the two variables is important.

You can also use the Sort stage to put them in order and add a Key Change column, which would make your stage variable derivation even easier (you would only need one).

Hi Pradeep,

u didnt mentioned source if it's database we can generate sequence no at source stage using user defined query:

I have requirement to generate the sequence number for the duplicate enteries.

select ID,RANK() OVER (PARTITION BY ID ORDER BY ID) Seq_number from schemaname.tabname

OR

using stages:
seq.file----->cpy------>aggr(grpby.key ID and caltype=count)--->seq
| |
| |
|
(Key ID)Joinstage(inner)|

In Aggr u can use grouping id as ID and calc type=countrows

In join stage inner join with key column as ID


Regards,
Rajesh