Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcement: Certain actions are currently causing a Page Not Found error. This is a known issue and we are working with the platform vendor to investigate and resolve it.
dukane24
New Contributor III

Filling dates between start and end date

Hello,

I have a table that contains items with Start and End dates (Table 1 below) that I need to link to a table that contains a single date for each item.  Basically if Table 1 has a StartDate of 10/1 and EndDate of 10/5, I need it to be associated with an item in Table 2 with all of the dates in between (10/1, 10/2, 10/3, 10/4, 10/5).  I believe the solution is to generate rows for all of the "in between" dates, but I'm not sure how to do this.  Any help you can provide would be much appreciated! Thank you -

   

Table 1:
StartDateEndDate
10/1/201710/5/2017
10/7/201710/10/2017
10/15/201710/20/2017

   

Table 2:
Date
10/1/2017
10/2/2017
10/3/2017
10/4/2017
10/5/2017
10/6/2017
10/7/2017
Etc…
1 Solution

Accepted Solutions

Re: Filling dates between start and end date

May be this?

LOAD Date(StartDate + IterNo() -1) as Date

While StartDate + IterNo() -1 <= EndDate;

LOAD * Inline [

StartDate, EndDate

10/1/2017, 10/5/2017

10/7/2017, 10/10/2017

10/15/2017, 10/20/2017

];

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
4 Replies
tester_sogeti
Contributor

Re: Filling dates between start and end date

Maybe this can help you. Don't focus on the answer read all comments.

edit number format valueloop

Hope it helps

Lluís

Re: Filling dates between start and end date

May be this?

LOAD Date(StartDate + IterNo() -1) as Date

While StartDate + IterNo() -1 <= EndDate;

LOAD * Inline [

StartDate, EndDate

10/1/2017, 10/5/2017

10/7/2017, 10/10/2017

10/15/2017, 10/20/2017

];

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
antoniotiman
Honored Contributor III

Re: Filling dates between start and end date

If You want from 10/1/2017 to 10/20/2017 then

SET DateFormat='MM/DD/YYYY';
LOAD Date(StartDate+IterNo()-1) as Date
While StartDate+IterNo()-1 <= EndDate;
LOAD Min(StartDate) as StartDate,Max(EndDate) as EndDate
Inline
[
StartDate, EndDate
10/1/2017, 10/5/2017
10/7/2017, 10/10/2017
10/15/2017, 10/20/2017]
;

dukane24
New Contributor III

Re: Filling dates between start and end date

Thank you! This worked perfectly!