topic Re: How to do Computation after taggregate row in Talend Studio

How to do Computation after taggregate row

Anonymous — Mon, 28 Oct 2019 15:15:30 GMT

Based on group by and sum function from tAggregate row i got below output. Now i need to get a single amount

Settled-Refund . Which component should be used

|=----------+--------=|
|TOTAL_PRICE|INDICATOR|
|=----------+--------=|
|4352.99 |SETTLED |
|650.46 |REFUND |'-----------+---------'

[statistics] disconnected

Re: How to do Computation after taggregate row

fdenis — Mon, 28 Oct 2019 15:20:35 GMT

it's easy to make calculation on one row.
so use tDenormalize ro have:
SETTLED | REFUND
4352.99 | 650.46

then use tmap.
good luck

Re: How to do Computation after taggregate row

Anonymous — Mon, 28 Oct 2019 15:28:50 GMT

Can u pls elaborate on the same

Re: How to do Computation after taggregate row

Anonymous — Mon, 28 Oct 2019 15:33:20 GMT

I assume you mean that you want to return the value of Settled-Refund on its own? If so, this can be done quite easily. I assume that Settled is always returned first? If so, you can extrapolate from this basic example.

In this example I have the following flow. The tFixedFlowInput component returns one column with two rows. I am subtracting the second value from the first value. The subtraction takes place in the tMap.

Here is the tMap.....

Here I am making use of tMap variables. tMap variables have some very useful features. They are processed in order (top down) and they hold their values between rows. So, I have set up two Double values (the Nullable flag is set to true....this is important). The Record1 value is sent to the SecondValue variable and the SecondValue variable sets the value of the FirstValue variable. As these are set in order (top down), the first row will have the FirstValue variable set to NULL and the SecondValue variable set to the value of the first row. The second row will have the FirstValue variable set to the first row value and the SecondValue variable will be set to the second row value. The output filter on the Out1 output means that the first row value is not returned because we know that the FirstValue variable will always be NULL. Therefore we only return the second row and this will return the result of ....

Var.FirstValue-Var.SecondValue

Re: How to do Computation after taggregate row

Anonymous — Mon, 28 Oct 2019 15:49:24 GMT

Its working thanks .But a supercomplicated way to do a simple stuff !
Also this may not work if i need to aggregate more rows with different Types .

Isnt there a simpler way to do it in Talend, I was looking for something like below :

sum (row [INDICATOR='SETTLED'] /TOTAL_PRICE -row [INDICATOR='REFUND'] /TOTAL_PRICE)

Can u suggest something else as well

Re: How to do Computation after taggregate row

fdenis — Mon, 28 Oct 2019 16:07:31 GMT

using tDenormalize after taggregate is working fine even if you have multiple data to sum or to remove.

Re: How to do Computation after taggregate row

fdenis — Mon, 28 Oct 2019 16:13:37 GMT

what a dirty way…

Re: How to do Computation after taggregate row

Anonymous — Mon, 28 Oct 2019 16:24:18 GMT

Sorry @fdenis, I wrote that before I saw your answer. I like to show off the functionality of the tMap, as it can massively help people with other computational problems. This method is ideal for comparing rows and only requires 1 component. However, your method was written first, so I will award the solution to you.

Re: How to do Computation after taggregate row

Anonymous — Mon, 28 Oct 2019 16:32:49 GMT

@dkdarshu45 wrote:

Its working thanks .But a supercomplicated way to do a simple stuff !
Also this may not work if i need to aggregate more rows with different Types .

Isnt there a simpler way to do it in Talend, I was looking for something like below :

sum (row [INDICATOR='SETTLED'] /TOTAL_PRICE -row [INDICATOR='REFUND'] /TOTAL_PRICE)

Can u suggest something else as well

@fdenis showed a simpler way of doing this.

Given your example equation, you could approach this in many ways. However, you told us that you had two rows where the first row needed the second row subtracted from it. The method I gave is the most efficient way (a single component) I could think of at the time for achieving that.

Re: How to do Computation after taggregate row

Anonymous — Fri, 01 Nov 2019 12:08:45 GMT

I am not able to do using tdenormalize. Can u post snapshot of the same

Re: How to do Computation after taggregate row

fdenis — Mon, 04 Nov 2019 09:02:18 GMT

https://help.talend.com/reader/wDRBNUuxk629sNcI0dNYaA/K4daeny9X_pgAhOiBR~Kug

Re: How to do Computation after taggregate row

fdenis — Mon, 04 Nov 2019 09:03:05 GMT

to get help select component into your job then press F1.