How to create multiple records based on each record loaded

[#1]

I have a table with multiple text fields and start & end date fields.

I want to expand the table to create a record for each date between the start & end date.

The source table has multiple rows.

Simple example:

Field      Start                End

A1      01/01/2013      03/01/2013

A2      06/01/2013      07/01/2013

Result:

Field      Start                Date

A1      01/01/2013      01/01/2013

A1      01/01/2013      02/01/2013

A1      01/01/2013      03/01/2013

A2      06/01/2013      06/01/2013

A2      06/01/2013      07/01/2013

Thanks

[datanibbler · Accepted solution]

Hi Alan,

check this (has been proposed to myself here, I have used it to blow up a masterdata_table with a start_date and end_date like yours to have one record for every day inbetween:

LOAD
       ...

      start_date

      end_date

     DATE(start_date+ IterNo()-1) as date,
RESIDENT Basisdaten_Zuordnungen_pre
While IterNO() <= (TODAY() - start_date+1)

You could replace TODAY() with end_date in that WHERE_clause, but in my instance I did not want records for the days in future (end_date is, for employees, often set to sth. in the far future, so that's a lot of unnecessary data)

Best regards,

DataNibbler

;

[#3]

Many thanks, worked perfectly.

Much appreciate your help.