Logic to count repeated rows
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 35
- Joined: Mon May 06, 2013 5:59 am
Logic to count repeated rows
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
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
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".
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 35
- Joined: Mon May 06, 2013 5:59 am
-
- Participant
- Posts: 35
- Joined: Mon May 06, 2013 5:59 am
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: