Stage Variable Logic Help

[rcil]

Hello All,

I need some help in completing the below task. The way I tried is not working.

Col1 Col2 Col3 Col4
ABC123 903534 0 10
ABC123 903534 0 12
ABC123 903534 1 14

ABC222 903534 1 30
ABC333 12345 0 5
ABC444 222333 0 5

Code: Select all

If the col3 = 0 then col4 = 0 and if previous.col1 = current.col1 and previous.col2 = current.col2 and If current.col3 = 1 then 
sum( previous.Cols4) + Current.Col4 and store in the current column

Expected Result:

ABC123 903534 0 0
ABC123 903534 0 0
ABC123 903534 1 36 (10+12+14)

ABC222 903534 1 30
ABC333 12345 0 0
ABC444 222333 0 0

I tried with StageVariable but somehow I am able to sum only the two rows . (Ex: svCol3)

Code: Select all

sv2: If sv1='Y' and svCol3 = 0 then sv2+ InLInk.Col4 else InLInk.Col4
sv1: If prev.col1 = curr.col1 and previous.col2 = current.column2 then 'Y' else 'N'

The Col3 values are populated with the stage variable operations in the same transformer. Does that make any difference?

What is the order we have to follow on StageVariables? Is it from top to bottom or down to up?

thanks

[ArndW]

rcil,

try using the following stage variables:

Code: Select all

[u]StageVar[/u]         [u]Value[/u]
Col4Sum          IF In.Col1:In.Col2 = LastCheckString THEN Col4Sum += In.Col4 ELSE 0
LastCheckString  In.Col1:In.Col2

And in the transform output for Col4 put

Code: Select all

IF In.Col3=1 THEN Col4Sum ELSE In.Col4

[Sainath.Srinivasan]

It is top to bottom.

From the look of the sample data, it appears that you want the total when col3 = '1'.

So you can do something like

stgSum <- (default) 0
stgOutSum <- (default) 0

stgSum <- If link.Col3 = '1' Then link.Col4 Else stgSum + link.Col4
stgOutSum <- If link.Col3 = '1' Then stgOutSum = stgOutSum + stgSum Else stgSum

[rcil]

I think it is fortunate that both of you guys posted your 665th post to help me. Both the logics worked perfectly well.

Thanks to both of you.
rcil

[chulett]

Agreed - what if both had been their 666th?