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
Create Sequence Number for the Duplicate Enteries
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 92
- Joined: Wed Mar 05, 2008 4:09 am
- Location: chennai
- Contact:
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.
"svSeqNumber" derivation : "if In.ID = svLastID THEN svSeqNumber+1 ELSE 1"
"svLastID" derivation "In.ID"
The order of the two variables is important.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Re: Create Sequence Number for the Duplicate Enteries
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
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
prajesh
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.