Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Calculated Parameters?

  1. #1
    Join Date
    Apr 2013
    Posts
    19

    Default Calculated Parameters?

    Hi All,

    I have an insert update transformation that queries my main DB that looks similar to this.

    select [fields] from [table] where [field1] > '${[parameter1]}'

    In my transformation's settings I have a the parameter1 there.

    Currently, when I move the data into my reporting db I manually input the number in the value when running the job.

    Is there a way for me to have a calculated parameter in place that gets the max value from my reporting table and input the value into "parameter1"?

    Any help is appreciated!

    Thanks --John

  2. #2
    Join Date
    Dec 2012
    Posts
    121

    Default

    Try to define the Max Aggregate in your schema from the Workbench!

    Yamas

  3. #3
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    You can cascade two Table Input steps, the first one providing a single field:

    Code:
    SELECT MAX(mod_date) max_date FROM target_table
    The second step grabs the delta set from your source table:

    Code:
    SELECT ... FROM source_table WHERE mod_date > ?
    Just configure the second step to read the parameter from the previous step's field.
    So long, and thanks for all the fish.

  4. #4
    Join Date
    Apr 2013
    Posts
    19

    Default

    Quote Originally Posted by marabu View Post
    You can cascade two Table Input steps, the first one providing a single field:

    Code:
    SELECT MAX(mod_date) max_date FROM target_table
    The second step grabs the delta set from your source table:

    Code:
    SELECT ... FROM source_table WHERE mod_date > ?
    Just configure the second step to read the parameter from the previous step's field.
    Thanks marabu... how do I configure the second step to read from the previous step?

  5. #5
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Just select the previous step from the dropdown list, but most likely you already found out by yourself ...

    My input step "Get Max" is emitting a single row containing a single field.
    Attached Images Attached Images  
    So long, and thanks for all the fish.

  6. #6
    Join Date
    Apr 2013
    Posts
    19

    Default

    Hahah yes I did! I was in the middle of replying now... thanks a lot for your help... this is going to be a huge asset for me... I can now automate my entire ETL process!

    Appreciate your help!

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.