Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Not applicable

How to create multiple records based on each record loaded

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

Labels (1)
1 Solution

Accepted Solutions
datanibbler
Esteemed Contributor

Re: How to create multiple records based on each record loaded

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

;

2 Replies
datanibbler
Esteemed Contributor

Re: How to create multiple records based on each record loaded

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

;

Not applicable

Re: How to create multiple records based on each record loaded

Many thanks, worked perfectly.

Much appreciate your help.