Generate sequence number for the same group

[suresh_dsx]

I need generate the sequence numbers based on the same value in the data stage. Tried using the sequence number generator but it is create sequence numbers but not generating based on the same value.

and also tried using the seq generating numbers in the transformer stage and that one also not working.

Code: Select all

@PARTITIONNUM + ((@INROWNUM-1) * @NUMPARTITION) +1

I searched in the dsx, did not find the options for sequence generator
Input one column:

'ABC'
'ABC'
'ABC'
'ABC'
'bBC'
'bBC'
'kBC'
'dBC'
'dBC'

Output

1,'ABC' AS new
2,'ABC' AS new
3,'ABC' AS new
4,'ABC' AS new
1,'bBC' AS new
2,'bBC' AS new
1,'kBC' AS new
1,'dBC' AS new
2,'dBC' AS new

[naveenkumar.ssn]

Hi

you have to use stage variable in the transfomer to get this done.Which I think is the easiest way to perform(though !!! there are lot of ways)

In the transformer have 4 stage variable

Assign a stage variable 1 = cntvariable as 0
prevrec=currentrecord
currentrecord={ the input from the source}
SameRecChck=If the previous rec is equal to the current records increment the counter else 0

Before you input the records to the transformer ..sort the input columns without fail.

The above logic would definitely work !!!

Regards
Naveen

[suresh_dsx]

Thanks for the valuable suggestion. Tried same in the transformer stage with four variables but the output not same.

Assign a stage variable 1 = cntvariable as 0
prevrec=currentrecord
currentrecord={ the input from the source}
SameRecChck=If the previous rec is equal to the current records increment the counter else 0


cntvariavle = 0
pre_val= cur_val
cur_val=source_input_col
same_rec_Chk=If pre_val=cur_val then 1+cntvariavle else 0

Output:
0'ABC' AS new
1'ABC' AS new
1'ABC' AS new
1,'ABC' AS new
0,'bBC' AS new
1,'bBC' AS new
0,'dBC' AS new
1,'dBC' AS new
0,'kBC' AS new

[chulett]

The above logic would definitely work !!!

They would stand a better chance of working if you order the stage variables properly.

And you only need two:

svCount: If InputValueFromLink = svPrevVal then svCount+1 else 1
svPrevVal: InputValueFromLink

Your sorting will affect this as will your partitioning so make sure those are correct as well. You can also use a Sort stage with the Key Change property set before this so that your check only requires one stage variable.

[naveenkumar.ssn]

Hi,

Yes its working.

I hope the one who created this thread can look upon the above solution to fix his problem

Thanks

Regards
Naveen