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
 

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