topic Re: Filling dates between start and end date in QlikView

Filling dates between start and end date

dukane24 — Tue, 17 Oct 2017 15:42:02 GMT

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:
StartDate	EndDate
10/1/2017	10/5/2017
10/7/2017	10/10/2017
10/15/2017	10/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…

Re: Filling dates between start and end date

Anonymous — Tue, 17 Oct 2017 15:48:24 GMT

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

Anil_Babu_Samineni — Tue, 17 Oct 2017 15:56:19 GMT

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

];

Re: Filling dates between start and end date

antoniotiman — Tue, 17 Oct 2017 16:18:09 GMT

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];

Re: Filling dates between start and end date

dukane24 — Tue, 17 Oct 2017 17:17:08 GMT

Thank you! This worked perfectly!