Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Read Formulas from Property File and calculate accordingly in table

  1. #1
    Join Date
    Aug 2013
    Posts
    139

    Default Read Formulas from Property File and calculate accordingly in table

    Hello EveryOne,


    we have a requirement to read formulas from Property file and calculate accordingly
    Key --- Value
    Meter1 -- Meter2
    Meter4 --- Meter3+Meter5


    Formula: Meter1=meter1-meter2, Meter4=meter4-(meter3+meter5)

    table1 inputwe have 100 meters) but had only 13 formulas
    Meter Id units time
    meter1 20 08-04-2014 10:58
    meter2 10 08-04-2014 10:59
    meter3 40 08-04-2014 10:57
    meter4 80 08-04-2014 10:59
    meter5 25 08-04-2014 10:59
    Desired Output

    Meter Id units Actual Units time
    meter1 20 10 08-04-2014 10:58
    meter2 10 10 08-04-2014 10:59
    meter3 40 40 08-04-2014 10:57
    meter4 80 15 08-04-2014 10:59
    meter5 25 25 08-04-2014 10:59
    Help needed

    This was Actually Extension other Thread but i tried to make it simple, pardon me if i was wrong
    Last edited by suresh84c; 04-11-2014 at 01:34 AM.
    Thank U For Your Time
    Suresh

  2. #2
    Join Date
    Sep 2013
    Posts
    235

    Default

    Looks like you need dynamic transformation generation. My opinion that would be much easier to do in raw java code, than trying to use kettle for this.
    I would first read all file with formulas - and create calculation flow for every formula.
    If this file is static - you may create a static transformation. If it is changes over time - you need a mechanism to re-assemble rows flow according to formulas changes.
    Then - read data from fields file and process all formulas - in one transformation.
    If you have a really huge amount of fields and slowly changing formulas file you may consider to change kettle (transformation rows) workflow manually.
    If not - I would start to solve this feature using API to create transformations programmatically. And this will be heavy development task.

  3. #3
    Join Date
    Aug 2013
    Posts
    139

    Default

    Thanks Dzmitry,
    Really appreciated for quick response

    we did everything in complete java, but requirement is to move to etl tools (kettle),

    The formula file is slowly Changing (i would say no change at all in existing but may add new formula)
    Could you please elaborate your suggested solution for this case
    Thank U For Your Time
    Suresh

  4. #4
    Join Date
    Sep 2013
    Posts
    235

    Default

    In case data flow and formulas is slowly changing - that would be easy.

    Create a transformation that will read input data in a row structure like:

    meter1/ units / Actual Units(will be calculated) / time / meter2 ...

    for you example this will be about 5*4=20 fields - and do calculations in one row. Calculate units based on meter1 and meter2 (mter1+meter2) in one data row.

    The main advantage that you will achive - is to reuse this transformation for multiple table inputs with different 'units' values.
    For the first look it may be done with 'Row denormalizer' step. Check transformation samples that you have in /samples folder

  5. #5
    Join Date
    Aug 2013
    Posts
    139

    Default

    Thanks Dzmitry

    The Data Flow table 1 is loaded every hour with 100 meters data for that hour, only the formula file is static(in some cases Slowly changing)

    any way I am trying the way you suggested and work to improvise, really appreciated for suggestion
    Thank U For Your Time
    Suresh

  6. #6
    Join Date
    Aug 2013
    Posts
    139

    Default

    Thanks Dzmitry,

    I created a transformation to get data as meter1/ units / Actual Units(will be calculated) / time / meter2 ...
    and done required
    calculation but i not able to put those in normalized form back as
    meter1/Aunits/timestamp
    meter2
    .
    ...
    ,,
    i tried row normalizer but failed to achieve
    Please find Attached Ktr as reference
    Actual_Values.ktr
    Thank U For Your Time
    Suresh

  7. #7
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    If you read the other thread on this, Marabu provided a functional solution...
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

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.