key generate

[shrey3a]

Hi,


I've a scenario as below

EMP id

123
123
456
456
222
234

I need to o/p in the format

emp id key

123 1
123 2
456 1
456 2
222 1
234 1

The logic is to start the seq again from 1 when ever the emp id changes.

Thanks in advance.....
Regards

[kcbland]

Search the forum for the numerous examples of using stage variables on sorted data to assign the ranking number. It's very easy, you'll need the data sorted in the desired order and then setup 3 stage variables to do the work.

[shrey3a]

Thanks ken...i found one...
Thanks Again


[quote="kcbland"]Search the forum for the numerous examples of using stage variables on sorted data to assign the ranking number. It's very easy, you'll need the data sorted in the desired order and then setup 3 stage variables to do the work.[/quote]

[Luciana]

Use stage variables on sorted data.
Ex.: varEmp (@Null) := RowProcGetPreviousValue(ENTRADA.id)
varSeq (0) := if ENTRADA.id = varEmp then varSeq +1 else 1
--------------------------------------------------------
Target id_key := ENTRADA.id : varSeq

[ashokyadav]

[quote="Luciana"]Use stage variables on sorted data.
Ex.: varEmp (@Null) := RowProcGetPreviousValue(ENTRADA.id)
varSeq (0) := if ENTRADA.id = varEmp then varSeq +1 else 1
--------------------------------------------------------
Target id_key := ENTRADA.id : varSeq[/quote]
-----------------------------------------------------------------------
I am new to the datastage , Where should this variable be defined? In job properties --> job control ?

[Luciana]

Hi ashokyadav

No, this variable be defined in stage transformer.