topic Re: Creating month end dates till latest month in QlikView

Creating month end dates till latest month

siddharth_kulka — Sat, 16 Nov 2024 18:17:19 GMT

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.

Re: Creating month end dates till latest month

vivek_gajjar01 — Thu, 30 Jul 2020 11:18:36 GMT

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;

Re: Creating month end dates till latest month

siddharth_kulka — Thu, 30 Jul 2020 13:50:17 GMT

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!

Re: Creating month end dates till latest month

siddharth_kulka — Thu, 30 Jul 2020 16:55:25 GMT

Works perfectly, thanks a lot.