Can we do sum in transformer stage?If yes how?
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 92
- Joined: Mon May 07, 2007 4:26 am
Can we do sum in transformer stage?If yes how?
Hi,
Can we do aggregation(sum) in transformer stage.If yes pls help me how to do?
thanks in advance
Can we do aggregation(sum) in transformer stage.If yes pls help me how to do?
thanks in advance
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
-
- Participant
- Posts: 132
- Joined: Wed Mar 01, 2006 11:12 pm
- Location: Pune
Re: Can we do sum in transformer stage?If yes how?
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.sureshchandra wrote:Hi,
Can we do aggregation(sum) in transformer stage.If yes pls help me how to do?
thanks in advance
SMB
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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: 92
- Joined: Mon May 07, 2007 4:26 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 92
- Joined: Mon May 07, 2007 4:26 am
-
- Participant
- Posts: 132
- Joined: Wed Mar 01, 2006 11:12 pm
- Location: Pune
Hi Sureshsachin1 wrote: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.
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.
SMB
-
- Participant
- Posts: 5
- Joined: Fri Mar 14, 2008 9:57 am
- Location: hyd
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 5
- Joined: Fri Mar 14, 2008 9:57 am
- Location: hyd