Running total of hours for ID at detail level - TmemorizeRows nogood

One Star

Running total of hours for ID at detail level - TmemorizeRows nogood

I have a detail transaction table that includes work hours for each work type. This detail is extracted into a new table with almost same info with new columns where total work hours for each work type are aggregated for that employee group. I need this detailed transaction record display the total work hours he does & total work hours recommended for that work type for comparison - BUT the total here is a running total of hours. Sample data posted -

NEW COLUMNS NEEDED HERE ARE : SUM_WORKED HOURS & SUM_RECOMMENDED HOURS - These are running totals for different clients that get added by all previous hours for that client. When client changes, the next records reset and new totals begin (BANK1, BANK2)
                                                                           
I have tried tMemorizeRows but it needs a row number defined to memorize specific number of rows (just comparing current and and a defined set of memorized rows works fine but data needed here has no preset number of rows per client  and worktype, hence I am stuck. Help please
 

                                                                                                 
Six Stars

Re: Running total of hours for ID at detail level - TmemorizeRows nogood

Use tagregaterows like you would agregate in  SQL also
One Star

Re: Running total of hours for ID at detail level - TmemorizeRows nogood

taggregate rows doesn't fit this situation..grouping by dimensions client & work type and totaling are at detail level and running.
 
Six Stars

Re: Running total of hours for ID at detail level - TmemorizeRows nogood

                                                

Starting job testagg at 16:45 18/05/2016.





connecting to socket on port 3831

connected

.-----+---+----+------+-----------.

| tLogRow_2 |

|=----+---+----+------+----------=|

|cust |emp|type|worked|recommended|

|=----+---+----+------+----------=|

|bank1|100|1 |1 |3 |

|bank1|100|1 |2 |2 |

|bank1|200|1 |3 |3 |

|bank2|100|1 |1 |2 |

|bank2|200|1 |1 |2 |

|bank1|100|2 |1 |3 |

|bank1|100|2 |2 |2 |

|bank1|200|2 |3 |3 |

|bank2|100|2 |1 |2 |

|bank2|200|2 |1 |2 |

|bank1|100|2 |1 |3 |

|bank1|100|3 |2 |2 |

|bank1|200|3 |3 |3 |

|bank2|100|3 |1 |2 |

|bank2|200|3 |1 |2 |

'-----+---+----+------+-----------'

.-----+---+----+------+-----------.

| tLogRow_1 |

|=----+---+----+------+----------=|

|cust |emp|type|worked|recommended|

|=----+---+----+------+----------=|

|bank1|100|3 |2 |2 |

|bank2|200|1 |1 |2 |

|bank1|200|1 |3 |3 |

|bank2|200|2 |1 |2 |

|bank2|200|3 |1 |2 |

|bank1|100|1 |3 |5 |

|bank1|100|2 |4 |8 |

|bank1|200|3 |3 |3 |

|bank1|200|2 |3 |3 |

|bank2|100|3 |1 |2 |

|bank2|100|2 |1 |2 |

|bank2|100|1 |1 |2 |

'-----+---+----+------+-----------'



disconnected

Job testagg ended at 16:45 18/05/2016.
One Star

Re: Running total of hours for ID at detail level - TmemorizeRows nogood

Thanks Deejay but that doesn't apply in my case. I figured it out based on forum notes
source DB --> tsort desc (Bank;employee,worktype) ---> tmap (source fields plus retrieve globalmapped variable a) --> tjavarow(derive cumulated value = current record value + global variable a; then map into global variable a based on new Bank,employee,worktype condition)--> output table
Understand tjavarow was tricky as I am not a Java guy but learning java a little bit helped in syntax.
Shong's article on one of the forums was very helpful.
Thanks

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch