Break operation in row wise comparison

[sreesuku2]

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

[prasson_ibm]

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

[chulett]

The comparison operation should break at the first instance itself.

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:

Code: Select all

svFirstDept =>  If IsNull(svFirstDept) then DEPT else svFirstDept
svIsBroken  =>  Not(svIsBroken) and DEPT <> svFirstDept

Then pass along rows which are "Not(svIsBroken)". Set the InitialValue of svFirstDept to @FALSE. Note that expressions automatically evaluate to true or false so (generally) there's no need to specifically use the System Variables or 0/1 and you can treat them as Boolean values - hence the suggestion to use "Not(x)" as the constraint rather than "If X then @FALSE else @TRUE".

The code should be pretty close if I understood the requirement.

[ray.wurlod]

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.

[ray.wurlod]

If you enclose in Code tags, you will get a readable format.

[sreesuku2]

Thanks Ray. I just changed the format

[chulett]

Make sure you define and initialize the variables properly. The first is a three character string set to @NULL and the second is an integer set to @FALSE in my case. This so svFirstDept shows 'ABC' all the way down.

[ray.wurlod]

Better to initialise to "???" rather than @NULL - comparisons to @NULL always return @NULL.

[chulett]

True... except for the fact that the code I posted specifically checks for null.

[chulett]

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:

Code: Select all

If SVPreEmp = SVCurrEmp then 1 else 0

Should be simply:

Code: Select all

SVPreEmp = SVCurrEmp

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.

[sreesuku2]

Sure craig.

I will take that point. It a valid one. Thanks for your inputs

-Sree

[chulett]

It's all about readability and clarity of purpose.

[asorrell]

It's all about readability and clarity of purpose.

As opposed to "job security"?

[chulett]

Exactly! Wait... what?

[ray.wurlod]

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).