Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: How to allocate header value to detail records

  1. #1
    Join Date
    Jan 2011
    Posts
    7

    Default How to allocate header value to detail records

    I have a transaction header with a tax amount that I want to allocate to the individual line items proportionately based on the value of each line item. For example, the header has a sales tax amount of $4.00. There are three associated line items:
    1 - value = $100
    2 - value = $50
    3 - value = $50

    I want to allocate the sales tax to each line as follows:
    1 - $2.00
    2 - $1.00
    3 - $1.00

    The only way I can think to do this is to have a first pass which writes everything to a temp table then does lookups to get the tax amount from the header and the total amount of the items ($200).

    Is there a way to keep the items in an array and loop through them multiple times without writing to the temp table?

  2. #2
    Join Date
    Sep 2009
    Posts
    810

    Default

    Hi there,

    if you can make sure that header records and detail records for a transaction are coming in groups, you could do some scripting, accumulate all transaction rows into an array, then calculate the share of tax for each item, finally write all transaction rows back to the stream.

    It can be done using the JavaScript step or UDJC, but since I'm in the midst of creating a Ruby scripting step for Kettle, and for the sole reason of being smug, I'll show how to do it using the Ruby step

    Here's the transformation demo, best watched in full screen:
    http://www.youtube.com/watch?v=xeVUs-196hw

    If anybody is interested in Ruby scripting for Kettle, check out the current progress on it at GitHub:
    https://github.com/type-exit/Ruby-Scripting-for-Kettle

    Cheers

    Slawo

  3. #3
    Join Date
    Mar 2010
    Posts
    159

    Default

    lol @ smug

  4. #4
    Join Date
    Mar 2010
    Posts
    159

    Default

    Also... if you think it's feasible to run a persistent perl interpreter with a user-defined perl step, let me know.

  5. #5
    Join Date
    Sep 2009
    Posts
    810

    Default

    Hi jbeal,

    I think you can put the idea of java perl interpreter to rest. There are only stale projects around for that.
    If it was feasible there would be something mature around I'm sure.

    Cheers

    Slawo

  6. #6
    Join Date
    Jan 2011
    Posts
    7

    Default

    Slawo,

    Thank you for your response. I didn't expect you to actually build the solution, great service!

    Ed

  7. #7
    Join Date
    Mar 2010
    Posts
    159

    Default

    Yeah I poked around and seems the only real persistent perl interpreter is mod_perl, short of really stale projects. Thought about trying to roll one up but it would not be trivial.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.