Subtraction of values using Stage variables in a transformer

[js_j]

Hi,

My file structure is

EMP NO Hours Type
1001 1 A
1001 2 B
1002 4 A
1002 5 B

My requirement for every occurrence of the employee number 1 the corresponding hours should be subtracted. There are only two occurences for every employee.

O/P should be
EMPNO Hours Type
1001 -1 X
1002 -1 X

I am trying to use a stage variable to do so.
kindly help me out for the same.


thanks,
JJ

[ray.wurlod]

It's very straightforward. Show us what you've attempted, and explain why it's not producing the expected output.

[kiran0435]

Job for this done as follows

Code: Select all

source--- sortstage-- transformer---removeduplicates--- target

In sort stage sort the data based on EMP NO and set Create Key Change column to True.

Then in Transformer use the following derivation in stage variables

stg1-- inputcol.hours

stg2-- if key change column=1 then stg1 else stg1-stg3

stg3-- stg1

Map stg2 to hours column

In the next Remove Duplicates stage retain the last column in the group. Then you wil get the required result.

[ray.wurlod]

If you give a man a fish he'll be fed briefly. If you teach a man to fish, he need never be hungry.

[SURA]

Yes Ray

You are right.

DS User