Current Row and Previous Row

Ahimsa · Post by **Ahimsa** » Mon May 07, 2007 10:16 pm

I want to develop a logic in which I check the column value of current row and earlier row. If its different, I want to increment the counter by 1.

e.g. Row 1.Name = A
Row2.Name = A
Row3.Name = B
Row4.Name = C

So here Name is changed from A to B to C, there I will be incrementing the counter by 1. If its same Name no counter increment.

Any suggestion on how to remember the column value of earlier row and compare it to current row.

Thanks.

nick.bond · Post by **nick.bond** » Mon May 07, 2007 10:51 pm

Stage variables are processed in order, you can therefore store values from the previous record in them

svPreviousValue = svCurrentValue
svCurrentValue = input.field
svValueHasChanged = If svCurrentValue = svPreviousValue Then 0 Else 1
svCounter = If svValueHasChanged Then svCounter + 1 Else svCounter

JoshGeorge · Post by **JoshGeorge** » Mon May 07, 2007 11:55 pm

In case you want to do this for record groups:

Use a sort stage. Sort records based on your key. Add SORT stage property Create Key Change Column. This property will add a column to your output link. The value of this column is 1 for the first record in the sort record group else the value is 0. In transformer use stage variables as stated in the above post. When a new record group comes the first record will be coming with key change column as 1, that time reset the svCounter to 1.

vmcburney · Post by **vmcburney** » Tue May 08, 2007 12:11 am

Since this is a parallel job make sure you have sorted and partitioned by your Name field so that matching rows are adjacent to each other and on the same node in the Transformer.

ray.wurlod · Post by **ray.wurlod** » Tue May 08, 2007 1:49 am

Use a Sort stage and let it generate the Sort Key Change column, which is exactly what you want.

Ahimsa · Post by **Ahimsa** » Tue May 08, 2007 10:37 am

Thanks all for your response. I got it working.
This site is always a huge help.

joesat · Post by **joesat** » Tue Oct 16, 2007 1:38 am

I have a similar kind of requirement so I thought I'd ask here itself. Which stage variable should we give in the derivation for the Output Columns? That is, should the stage variable prefixed 'prev' be used or the one with 'curr' be used?

When I use 'prev' the first row is displayed with empty rows and when I use 'curr' the values are not displayed as expected...if it is inappropriate to continue in this topic, let me know..I will start a new topic with complete details...