Surrogate key generator

[krisp321]

Hi

my source data is like for example

KeyColNm Surrogatekey
------------
A .............10
A .............20
A .............30
A .............40
B .............10
B .............20
B .............30
C .............10
C .............20
C .............30
C .............40
C ............. 50

Means, like my requirement is when data value changes in key column, then the surrogate value should start from initial values and should increment 10 when the value is repeating.

Any suggestions please

Thanks

[thompsonp]

Sort the data and choose to generate a key change column, then in a transformer derive the key values starting at 10 (when keyChange is 1) and incrementing by 10 (when keyChange = 0)

[ulab]

Yes that is Right, and if you want to generate with a increment of 1 then use the following as a stage varible in transformar "0 + @INROWNUM +(mod(@PARTITIONNUM,@NUMPARTITIONS)) + ((@INROWNUM-1) * (@NUMPARTITIONS-1))"

[vivekgadwal]

Yes that is Right, and if you want to generate with a increment of 1 then use the following as a stage varible in transformar "0 + @INROWNUM +(mod(@PARTITIONNUM,@NUMPARTITIONS)) + ((@INROWNUM-1) * (@NUMPARTITIONS-1))"

This derivation of yours works with only round-robin partitioning, correct?

[ray.wurlod]

Not correct.

[boringbaby]

I agree with thompsonp .
Doesn't it work?