Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
BrandonN
Contributor II
Contributor II

Populate list of Date based on 2 date

Hi All,

Need help on populate list of date based on 2 date.

Sample Input & excepted output as below:

Input:

HolidayStart DateEnd Date
A02-Jun-2110-Jun-21
B20-Jun-2125-Jun-21
C26-Jun-2126-Jun-21

 

Expected table output in Qlik Sense

HolidayDate
A02-Jun-21
A03-Jun-21
A04-Jun-21
A05-Jun-21
A06-Jun-21
A07-Jun-21
A08-Jun-21
A09-Jun-21
A10-Jun-21
B20-Jun-21
B21-Jun-21
B22-Jun-21
B23-Jun-21
B24-Jun-21
B25-Jun-21
C26-Jun-21

 

Appreciate that!

Thank you

1 Solution

Accepted Solutions
chrismarlow
Specialist II
Specialist II

Hi,

Happy Friday!

Try this;

data:
Load * Inline [
Holiday, Start Date,	End Date
A,	02/06/2021,	10/06/2021
B,	20/06/2021,	25/06/2021
C,	26/06/2021,	26/06/2021
];

dates:
LOAD
	date(mindate + IterNo()) AS Date
WHILE mindate + IterNo() <= maxdate
;
LOAD
  min([Start Date])-1 as mindate,
  max([End Date]) as maxdate
RESIDENT data;

left join (dates)
Load *
Resident data;

final:
NoConcatenate
Load
	Holiday,
    [Date]
Resident dates
Where Date>=[Start Date] and Date<=[End Date];

DROP TABLE data, dates;

Adapted from a calander script on another thread (The Fastest Dynamic Calendar Script (Ever) - Qlik Community - 1484721).

You may need to change dates/date formats ...

Cheers,

Chris.

View solution in original post

2 Replies
chrismarlow
Specialist II
Specialist II

Hi,

Happy Friday!

Try this;

data:
Load * Inline [
Holiday, Start Date,	End Date
A,	02/06/2021,	10/06/2021
B,	20/06/2021,	25/06/2021
C,	26/06/2021,	26/06/2021
];

dates:
LOAD
	date(mindate + IterNo()) AS Date
WHILE mindate + IterNo() <= maxdate
;
LOAD
  min([Start Date])-1 as mindate,
  max([End Date]) as maxdate
RESIDENT data;

left join (dates)
Load *
Resident data;

final:
NoConcatenate
Load
	Holiday,
    [Date]
Resident dates
Where Date>=[Start Date] and Date<=[End Date];

DROP TABLE data, dates;

Adapted from a calander script on another thread (The Fastest Dynamic Calendar Script (Ever) - Qlik Community - 1484721).

You may need to change dates/date formats ...

Cheers,

Chris.

BrandonN
Contributor II
Contributor II
Author

Thanks for the solution and suggestion on the dynamic calendar!

Appreciate it  & have a good weekend!