Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Split date range question

  1. #1

    Default Split date range question

    Hi I am new to Pentaho Data integrator and I have a question.

    If i have a value in a table with start date, end date, and total amount
    for example 1 Jan 2016, 31 Dec 2016, and total amount is 365,000

    is it possible to have PDI to create 365 records ( for the number of days bet 1 Jan to 31 Dec) and compute a field with the value of 365,0000/365?
    and store that into a database table ?

  2. #2
    Join Date
    Jan 2015
    Posts
    107

    Default

    It is possible, of course. That said, you will probably be able to do that quicker in Excel or a SQL query.

    Also please note that 2016 is a leap year and has 366 days.

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

    Default

    There's a step named Clone-Row we use for building date dimensions.
    Attached Files Attached Files
    So long, and thanks for all the fish.

  4. #4

    Default

    Quote Originally Posted by Isha Lamboo View Post
    It is possible, of course. That said, you will probably be able to do that quicker in Excel or a SQL query.

    Also please note that 2016 is a leap year and has 366 days.
    Hi Isha

    Thank you. It is possible to do it in Excel or SQL, but my team only has these fields and we need to get the individual records and some extending 20-30 years. The next question I have is whether we can determine the no of days in by calculation.

    Thank you.

  5. #5
    Join Date
    Jan 2015
    Posts
    107

    Default

    I created a transformation that does what you asked last night, but didn't have time to post.

    I see Marabu already did it, so you're probably done already, but here is my version anyway.

    create_date_records.ktr

  6. #6

    Default

    Thank you very much. I have two questions.

    1. Where and how to we tell the system to loop and stop correctly?
    2. I added a second row in the initial data and I got an error. Do I have to do something to process multiple rows? Is it because I ran it once, I cannot run it again?

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.