
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Creating month end dates till latest month
Hello - Seeking guidance to address a last minute request.
Source Table (A) has 2 fields - Distinct Parent Name and just one Month (Month end) for each parent.
Problem Statement (In Bank-end Script):
Need to create a separate table (Final) in a way such that month end dates are created for every Parent (from table A) from the associated date to the latest (current) month (7/31/2020).
Example:
Table A:
Parent | Month |
FB | 4/30/2020 |
APPL | 6/30/2020 |
Expected Output:-
Final:
Parent | Month |
FB | 4/30/2020 |
FB | 5/31/2020 |
FB | 6/30/2020 |
FB | 7/31/2020 |
APPL | 6/30/2020 |
APPL | 7/31/2020 |
Any assistance would be helpful, thank you.
- Tags:
- qlikview_scripting
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
One way you do it would be through the use of a cartesian join and then filter out the data accordingly. So something like the below:
tempMonths:
LOAD DISTINCT
min(Month) as MinMonth
FROM ...DataTableSource;
let vMaxdate = num(monthend(today()));
let vMindate = num(peek('MinMonth',0,'tempMonths'));
TRACE Min Month: $(vMindate);
Data:
LOAD Parent,
Month
FROM ...DataTableSource;
JOIN (Data)
Datefield:
LOAD DISTINCT MonthEnd(Datefield) as MonthEnd
;
LOAD date($(vMindate)+IterNo()-1) AS Datefield
AUTOGENERATE (1)
WHILE $(vMindate)+IterNo()-1<= $(vMaxdate);
NoConcatenate
FinalData:
LOAD *
Resident Data
WHERE MonthEnd>=Month;
Drop Tables Data, tempMonths;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
One way you do it would be through the use of a cartesian join and then filter out the data accordingly. So something like the below:
tempMonths:
LOAD DISTINCT
min(Month) as MinMonth
FROM ...DataTableSource;
let vMaxdate = num(monthend(today()));
let vMindate = num(peek('MinMonth',0,'tempMonths'));
TRACE Min Month: $(vMindate);
Data:
LOAD Parent,
Month
FROM ...DataTableSource;
JOIN (Data)
Datefield:
LOAD DISTINCT MonthEnd(Datefield) as MonthEnd
;
LOAD date($(vMindate)+IterNo()-1) AS Datefield
AUTOGENERATE (1)
WHILE $(vMindate)+IterNo()-1<= $(vMaxdate);
NoConcatenate
FinalData:
LOAD *
Resident Data
WHERE MonthEnd>=Month;
Drop Tables Data, tempMonths;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yet to implement and try it out, but the logic seems right so this should work. Will revert back shortly with findings.
In hindsight, doing something similar while creating a master calendar in multiple apps but didn't think of leveraging the same to create month end dates. Thanks for the guidance!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Works perfectly, thanks a lot.
