How to do Computation after taggregate row

[Anonymous]

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

[fdenis · Accepted solution]

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

[Anonymous]

Can u pls elaborate on the same

[Anonymous]

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

[Anonymous]

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]

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

[fdenis]

what a dirty way…

[Anonymous]

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.

[Anonymous]

---

@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. 

[Anonymous]

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