calcul the sum

One Star

calcul the sum

hello;
i have more than tow tables in a data base (Access). it have the same structure.
I would like calculate the sum total of a columun ("net" for example) for all tables and put the result in a variable.
Can you please help me
One Star

Re: calcul the sum

Hi,
you could connect different inputs with same structure with tUnite. Than you could use tAggregateRow to calculate the sum.
I think something like the following should work too:
SELECT sum(net) FROM (SELECT net FROM tab A UNION SELECT net FROM tab b)
Bye
Volker
One Star

Re: calcul the sum

hi Volker;
i think i can't use tUnit because in reality i used tFlowToIterate to iterate my tables.
So what how can you advice me.
Thank you for your help.
One Star

Re: calcul the sum

Hi,
OK, it is not as easy as i thought ;-)
You could use a temporary file (with append) and than calculate the sum.
Or you could use a context variable and calculate the value in a tJavaRow (context.sum+= actualValue)
Bye
Volker
One Star

Re: calcul the sum

hi volker;
i don't know how to use exactely a context variable.
can you please give me an exemple.
thank you.
One Star

Re: calcul the sum

Hi,
go to the context tab and add a line. Name it "sum" (for example).
Add a tJavaRow in your flow and copy each input line to the output:
output_row.varName= input_row.varName;

(with TOS 3.0.x press "Add code")
The last line should be:
context.sum+= input_row.varNameForSum

Add after your process one additional tJava with the following code:
System.out.println("The sum is: " + context.sum.toString());

Bye
Volker
One Star

Re: calcul the sum

hello Volker,
Thank you for your anser.Unfortunnatly it doesn't work.
I can't calculate the total sum
after running job i get this message:" The sum is " whitout the result
Can you help me please.
One Star

Re: calcul the sum

hi Volker;
i have yhis message exception when i execute my job:

Exception in component tJava_1
java.lang.NullPointerException
at jhgf.cc_0_1.cc.tAccessInput_1Process(cc.java:1045)
at jhgf.cc_0_1.cc.runJobInTOS(cc.java:1446)
at jhgf.cc_0_1.cc.main(cc.java:1355)
please can you help me
One Star

Re: calcul the sum

Could you please post a screen shot of your job. Looks like you try to use the sum before it is calculated.
Bye
Volker
One Star

Re: calcul the sum

hi Volker;
this is my job in 4 slots:
Thank you.
One Star

Re: calcul the sum

Hi,
looks like the tJava is executed before the tJavaRow. To get the sum at all add remove tJava from your stream and add it with an OnSubjobOk. This should work.
Bye
Volker
One Star

Re: calcul the sum

Hi Volker;
thank you for your help.I tr it but it dosn't work.
Could you please post a screen shot .
Thank you
One Star

Re: calcul the sum

Hi,
sorry for the late post. Last week half of my family was down with the flu.

So first I think I get the reason for the problem. I run into the self error at creating an example. It seems that you must define a initial value for your context variable. Talend is using Double and not the native type double. So the += operator throws a NullPointerException.
Anyway here is my solution:
Code in tJavaRow
output_row.orderId = input_row.orderId;
output_row.orderText = input_row.orderText;
context.sum+= input_row.orderId;

Code in tJava
System.out.println("The sum of all order ids is: " + context.sum);

And the output of the job:
1|Mr. Jones first order
2|Mr. Jones second order
3|The third order of Mr. Jones
6|Mrs. Tompson first ordr
7|Mrs. Tompson second order
8|Mrs. Tompson order number third
18|Mrs. Jones only one order
19|Mr. Albert only one order
20|Mrs. Albert first order
21|Mrs. Albert second order
22|Mrs. Albert third order
The sum of all order ids is: 127.0

The jobs selects for some customer orders from a database and calculates the sum of the customerId.
Bye
Volker
One Star

Re: calcul the sum

Hi Volker;
Sorry for my late. I have a problem in my Internet Connexion.
I hope in good health and your family too.
Thank you for your anser.It was a good solution.
Problem resolved.
Thank you
One Star

Re: calcul the sum

Thanks. This helped me also.