Split date range question

[effendi_baba]

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 ?

[Isha Lamboo]

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.

[marabu]

There's a step named Clone-Row we use for building date dimensions.

[effendi_baba]

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.

[Isha Lamboo]

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

[effendi_baba]

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?

[flashfs]

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

Thanks for that. You are creating 1 day more, but it's easy to fix.