Need to capture a running total at the end of a transform
Moderators: chulett, rschirm, roy
Need to capture a running total at the end of a transform
Hi all
I have 3 fields accumulating in a translate that I need to output to a sequential file at the end of the translate step. Any ideas?
I have 3 fields accumulating in a translate that I need to output to a sequential file at the end of the translate step. Any ideas?
rcasey
bless me o creator for I am a Newbie
bless me o creator for I am a Newbie
Let me explain. Yes it is a transformer step processing/formatting a large amount of data from a DB2 stage. I need the total amounts of three fields at the end for balancing purposes and would like to not have to aggregate the data due to the volume. I can pass a running total into the output link but still would have to deal with how to get that end total.
Thx
Thx
rcasey
bless me o creator for I am a Newbie
bless me o creator for I am a Newbie
Why don't you want to use the aggregator? To simply stream a few columns to it and group by a static column value, like 'X', will accumulate the totals for you and when the stage closes output that one row of totals. This is the simplest and most elegant solution.
Stage variables don't work for what you are doing.
Stage variables don't work for what you are doing.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
I really think you would be better off to implement what Ken has given you, but my feeling is that you do not want to use the agg stage so here is another option - not as elegant, but will get the job done.
This solution would actually combine Kim Duke's post where he suggests that you use stage variables and a Hash table.
1) In your transform stage hard code the value "TOTAL" in the derivation for the key column of the Hash table.
2) Add the stage variables that you want to accumulate (per Kim's suggestion)
3) Create 3 additional columns in the out link named -
TOTAL1
TOTAL2
TOTAL3
or a column for each total you want to accumulate.
4) Since Hash file writes are destructive you will end up with one (1) record in the Hash file keyed as TOTAL with the 3 columns of summed data.
5) Have the Hash file be the source to a simple sequential file
This is simply overkill when you could use the agg stage, but if you choose not to then this too will work.
Regards,
This solution would actually combine Kim Duke's post where he suggests that you use stage variables and a Hash table.
1) In your transform stage hard code the value "TOTAL" in the derivation for the key column of the Hash table.
2) Add the stage variables that you want to accumulate (per Kim's suggestion)
3) Create 3 additional columns in the out link named -
TOTAL1
TOTAL2
TOTAL3
or a column for each total you want to accumulate.
4) Since Hash file writes are destructive you will end up with one (1) record in the Hash file keyed as TOTAL with the 3 columns of summed data.
5) Have the Hash file be the source to a simple sequential file
This is simply overkill when you could use the agg stage, but if you choose not to then this too will work.
Regards,
Mike Hester
mhester@petra-ps.com
mhester@petra-ps.com
-
- Premium Member
- Posts: 385
- Joined: Wed Jun 16, 2004 12:43 pm
- Location: Virginia, USA
- Contact:
I can use the aggregator to make things easer but I think I will also try Chuck's suggestion and accumulate in the stage variables. If I read him right there is an aggregate last function or somthing like that, I have not seen that one before but on the other hand there is a mountain of "stuff"
I have not see yet.
Thanks for the replies!!
I have not see yet.
Thanks for the replies!!
rcasey
bless me o creator for I am a Newbie
bless me o creator for I am a Newbie
You don't need the stage variables. Just pass an 'X' as the value for a column 'GROUPBYME' in the output link to the aggregator. Setup 3 other columns to track the numbers you want.
In the aggregator stage set column GROUPBYME to have group-by checked and listed as the derivation. For the other three columns, derive them with SUM(thecolumnname1 or 2 or 3).
In the aggregator stage set column GROUPBYME to have group-by checked and listed as the derivation. For the other three columns, derive them with SUM(thecolumnname1 or 2 or 3).
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Premium Member
- Posts: 385
- Joined: Wed Jun 16, 2004 12:43 pm
- Location: Virginia, USA
- Contact:
Just because you use an aggregator, does not mean you have to use a group by. That is one of the nice things about "Last".
This is also a handy trick if you output based upon the difference between the previous row and the current row. Since the last row will never have something to trigger it (a next row), we can use the aggregator to be that trigger. Also, this may be memory efficient than using the aggregator with a "group by".
Chuck
This is also a handy trick if you output based upon the difference between the previous row and the current row. Since the last row will never have something to trigger it (a next row), we can use the aggregator to be that trigger. Also, this may be memory efficient than using the aggregator with a "group by".
Chuck