Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Need help on populate list of date based on 2 date.
Sample Input & excepted output as below:
Input:
Holiday | Start Date | End Date |
A | 02-Jun-21 | 10-Jun-21 |
B | 20-Jun-21 | 25-Jun-21 |
C | 26-Jun-21 | 26-Jun-21 |
Expected table output in Qlik Sense
Holiday | Date |
A | 02-Jun-21 |
A | 03-Jun-21 |
A | 04-Jun-21 |
A | 05-Jun-21 |
A | 06-Jun-21 |
A | 07-Jun-21 |
A | 08-Jun-21 |
A | 09-Jun-21 |
A | 10-Jun-21 |
B | 20-Jun-21 |
B | 21-Jun-21 |
B | 22-Jun-21 |
B | 23-Jun-21 |
B | 24-Jun-21 |
B | 25-Jun-21 |
C | 26-Jun-21 |
Appreciate that!
Thank you
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.
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.
Thanks for the solution and suggestion on the dynamic calendar!
Appreciate it & have a good weekend!