Can we do sum in transformer stage?If yes how?

[sureshchandra]

Hi,
Can we do aggregation(sum) in transformer stage.If yes pls help me how to do?


thanks in advance

[ray.wurlod]

Stage variable initialized to zero and added to as each row arrives on the input link.

[sachin1]

hello ray, please can you check what is issue with this code.

SQL> select deptno from emp order by deptno;

DEPTNO
----------
10
10
10
20
20
20
20
20
30
30
30

DEPTNO
----------
30
30
30

14 rows selected.

i have 2 stage variables val1 and val2 both initialized to 0.

job design is like oci--->transformer--->hash file.


in transformer i have done


derivation stage variable
------------------------------------------
DSLink2.deptno val1

If DSLink2.deptno =val1 Then val2
val2+1 else '0



output i have two columns deptno and val2.

my output shows like

deptno val2

10 3
20 8
30 14.


please guide me.

[baglasumit21]

Hi,
Can we do aggregation(sum) in transformer stage.If yes pls help me how to do?


thanks in advance

Yes it can be done in a transformer stage. You need to play around with the stage variables. Be sure that the source data is sorted on the keys you wish to aggregate on and then use stage variables to compare the key columns and sum the values for column if the key column are same.

[ray.wurlod]

Your original question did not mention grouping. Therefore I answered in the sense of a simple sum. You need sorted input and more stage variables to identify when the groups change so that you can reset the summation variable.

[sureshchandra]

Hi Ray,
thanks for ur response.
i have to do sum in Transformer.For eg:A+B=C.
Here A and B are columns which are there and C is the new column(sum(A+B)).So C is the new column i have to insert .


thanks in advance

[sachin1]

yes it can be done

[ray.wurlod]

And, to clarify your requirement, you want a separate sum for each combination of A and B. Is that correct?

[sureshchandra]

Hi all,
Thanks for ur help.Issue is resolved.

[baglasumit21]

hello ray, please can you check what is issue with this code.

SQL> select deptno from emp order by deptno;

DEPTNO
----------
10
10
10
20
20
20
20
20
30
30
30

DEPTNO
----------
30
30
30

14 rows selected.

i have 2 stage variables val1 and val2 both initialized to 0.

job design is like oci--->transformer--->hash file.


in transformer i have done


derivation stage variable
------------------------------------------
DSLink2.deptno val1

If DSLink2.deptno =val1 Then val2
val2+1 else '0



output i have two columns deptno and val2.

my output shows like

deptno val2

10 3
20 8
30 14.


please guide me.

Hi Suresh
Try this
derivation stage variable
------------------------------------------

if DSLink3.DEPTNO = stageVar2 Then StageVar1+1 else 1 StageVar1
DSLink3.DEPTNO StageVar2

This will give you the result as

10 1
10 2
10 3
20 1
20 2
20 3
20 4
20 5
30 1
30 2
30 3
30 4
30 5
30 6

Then you can write this output to a hash file with the dept_no as key
you will ge tthe output as

10 3
20 5
30 6.

[sachin1]

thanks a lot

[cpvnprasad]

Hi

I have another idea for this,there is an inbuilt aggregator which we will get the same result as you are looking for.

[ray.wurlod]

If it is resolved please mark the thread as Resolved.

cpvmprasad, the original question was very specific about "in Transformer stage".

[thurmy34]

Hi,
When i have to do this i use RowProcCompareWithPreviousValue.

[cpvnprasad]

Hi to all

Ray,i have done in the transformer stage itself.

actually the same problem i have faced previously,
by doing the same way i have done it...

Thanks