Create Sequence Number for the Duplicate Enteries

[pradeep_nov18]

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

[ArndW]

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.

[chulett]

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).

[prajesh]

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

[ray.wurlod]

U is a fairly common Burmese name. At least one of our posters is named U.

The second person personal pronoun in English is spelled "you". Not "u".
Please strive to maintain a professional standard of written English on DSXchange. It is not a mobile telephony device, and you are not limited to 140 characters, so there is no reason to employ SMS-style abbrevations.