Logic to count repeated rows

[Prince_Hyd]

Hello Folks

I have a scenario similar to this

Source Target
10 1
10 2
10 3
20 1
20 2
30 1
30 2

and i want the target as mentioned above

[eph]

Hi,

Use a transformer and variables to retain the previous value for compare :
Hash Partition & Sort on your input column
sv1=input column
sv2=if sv1<>sv3 then 1 else sv2+1 (default value 0)
sv3=input column

sv2 will give you the desired value

It is a classical design in DS

Eric

[chulett]

You can simplify this check by enabling the Create Key Change option in the Sort stage. Then your single stage variable check can be "set the running count to one when the KeyChange column is 1, when it is 0 increment the current count".

[Prince_Hyd]

Hello Eric

I got the solution by using the below mentioned logic

IF deptno = deptno Then deptno+1 Else 1

this one gave me exact solution.

Thanks for your reply

[chulett]

IF deptno = deptno Then deptno+1 Else 1

You might want to double-check the code you posted...

[Prince_Hyd]

Hello Chullet


i'm not getting wat you are saying



Thanks

[chulett]

Just saying that the derivation as posted makes no sense.

IF deptno = deptno Then deptno+1 Else 1

The bold part above needs to be some other variable. And (pet peeve alert) since the fake data you posted was only "similar" to whatever you are really doing, we've got no way to know what that other column name should be... other than saying it's probably what you labeled "Target".

As for the first "deptno" pair, I'm assuming you are doing change detection so the one on the left of the equal sign is the current value while the value to the right of the equal sign is the immediately previous value you've stashed in a stage variable.

I was just looking for more clarity so that if someone starting off with the product stumbled across your exact solution here it would make more sense.

[ray.wurlod]

Actually the test expression deptno = deptno will always be true, so your counter will increment for every row.

[chulett]

Of course. More pseudo-code than actual "code code" in this case, me thinks.

[eph]

And by the way, your code is the exact opposite of mine, inverted then&else and inverted internal logic, which is why it is also valid logic-wise.

Eric