Break operation in row wise comparison
Moderators: chulett, rschirm, roy
Break operation in row wise comparison
Hi Team,
I am trying to do a row wise comparison. Is there any way I can break the operation based on a condition ?
I have the below rows
Row | Dept | Hours
1 ABC 10
2 ABC 15
3 XYZ 10
4 ABC 15
5 ABC 20
I want to break the operation at row 3 where Dept Not equal to the following one. In this case ABC <> XYZ, then comparison should break. Event at row 4 also its XYZ <> ABC, but it should not go there. The comparison operation should break at the first instance itself.
My actual intent is to consider only those hours before the first dept change. Any other logic is also welcome
- Sree
I am trying to do a row wise comparison. Is there any way I can break the operation based on a condition ?
I have the below rows
Row | Dept | Hours
1 ABC 10
2 ABC 15
3 XYZ 10
4 ABC 15
5 ABC 20
I want to break the operation at row 3 where Dept Not equal to the following one. In this case ABC <> XYZ, then comparison should break. Event at row 4 also its XYZ <> ABC, but it should not go there. The comparison operation should break at the first instance itself.
My actual intent is to consider only those hours before the first dept change. Any other logic is also welcome
- Sree
-
- Premium Member
- Posts: 536
- Joined: Thu Oct 11, 2007 1:48 am
- Location: Bangalore
Hi Sree,
You can do this with the help of transformer using stage variables.
Store your current value to stage variable.
CURR=InputROW
COMPARISION=if (PREV<>CURR) THEN 1 ELSE 0)
PREV=CURR
In the constraint define CMPARISION=0
You can do this with the help of transformer using stage variables.
Store your current value to stage variable.
CURR=InputROW
COMPARISION=if (PREV<>CURR) THEN 1 ELSE 0)
PREV=CURR
In the constraint define CMPARISION=0
Thanks
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
Re: Break operation in row wise comparison
So... you only want the first two records and all records after the first 'break' should be skipped? If so you'll need different code... something like:sreesuku2 wrote:The comparison operation should break at the first instance itself.
Code: Select all
svFirstDept => If IsNull(svFirstDept) then DEPT else svFirstDept
svIsBroken => Not(svIsBroken) and DEPT <> svFirstDept
The code should be pretty close if I understood the requirement.
-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:
The important piece of information here is that you use the stage variable in a Constraint expression, so that no rows are transferred to output once your break condition has been met.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
If this is working for you then good. My only thoughts I've already posted... I educate people working for me to not use those " = 1 " or " = 0 " comparisons, they get called out and corrected during the peer review. One example:
Should be simply:
As with any other derivation that doesn't do an assignment, it will automagically evaluate to either @TRUE (1) or @FALSE (0). And the name of the stage variable should make it perfectly obvious what it means when it is true versus when it is false.
Code: Select all
If SVPreEmp = SVCurrEmp then 1 else 0
Code: Select all
SVPreEmp = SVCurrEmp
-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:
Some developers seem to have the attitude that lack of documentation = job security.
Anyone working for me produces the design and documentation first, before being given the chance to play with DataStage (or whatever tool).
Anyone working for me produces the design and documentation first, before being given the chance to play with DataStage (or whatever tool).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.