Creating Rownum

[pravin1581]

Hi All,

I have a requirement where i need to create row numbers for different data sets as the following :

Code: Select all

     R1        R2         R3          Rownum
   --------  ------      -----        ----------
     1          A          10               1
     1          A          20               2
     1          A          30               3
     2          B          10               1
     2          B          20               2
     2          B          30               3

using @INROWNUM creates the rownumber sequentially for the entire data set where as i need it for different partitions. Please help me out in this.

[georgesebastian]

Hi Pravin,

I think you need to first sort the data and then use stage variables here as well as the routine rowproc comparewithprevious value.

Thanks
George

[nick.bond]

George is right, first sort the data by key fields. I wouldn't use the row compare routine though as it will be quicker to use stage variables to compare the current value with the previous value.

svPreviousValue = svCurrentValue
svCurrentValue = input_rec.value

have the stage variables in that order and you can compare the two in another stage varible

svIsNewValue = If svPreviousValue = svCurrentValue Then 0 Else 1

As you are using EE make sure the partitioning is correct, i.e. partitioned by the key values. If you have used sort stage before the transformer DS should partition the data correctly for you without you having to manually specify it but if you get strange results check this in the SCORE DUMP.

[DSguru2B]

RowProcCompareWithPreviousValue() is only present in server jobs and not px jobs. So your only option is to use stage variables.

[pravin1581]

RowProcCompareWithPreviousValue() is only present in server jobs and not px jobs. So your only option is to use stage variables.

How can we create row number for the different data sets ?

[DSguru2B]

Did'nt Nick already answer your post?
What have you tried so far?

[nick.bond]

Ok you need one more stage varible to produce the row numbers

svRowNumber = If svIsNewValue Then 1 Else svRowNumber + 1