topic Re: calcul the sum in Talend Studio

calcul the sum

Anonymous — Sat, 16 Nov 2024 14:04:02 GMT

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

Re: calcul the sum

Anonymous — Fri, 20 Feb 2009 21:14:35 GMT

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

Re: calcul the sum

Anonymous — Sat, 21 Feb 2009 12:50:59 GMT

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.

Re: calcul the sum

Anonymous — Sat, 21 Feb 2009 20:36:32 GMT

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

Re: calcul the sum

Anonymous — Mon, 23 Feb 2009 07:52:13 GMT

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

Re: calcul the sum

Anonymous — Mon, 23 Feb 2009 20:43:09 GMT

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

Re: calcul the sum

Anonymous — Tue, 24 Feb 2009 09:29:22 GMT

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.

Re: calcul the sum

Anonymous — Tue, 24 Feb 2009 14:35:43 GMT

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

Re: calcul the sum

Anonymous — Thu, 26 Feb 2009 06:12:24 GMT

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

Re: calcul the sum

Anonymous — Thu, 26 Feb 2009 08:28:07 GMT

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

Re: calcul the sum

Anonymous — Thu, 26 Feb 2009 20:05:07 GMT

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

Re: calcul the sum

Anonymous — Fri, 27 Feb 2009 10:04:11 GMT

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

Re: calcul the sum

Anonymous — Tue, 03 Mar 2009 21:51:15 GMT

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

Re: calcul the sum

Anonymous — Fri, 13 Mar 2009 08:20:48 GMT

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

Re: calcul the sum

Anonymous — Tue, 30 Mar 2010 00:35:31 GMT

Thanks. This helped me also.