Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
siddharth_kulka
Creator II
Creator II

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:

ParentMonth
FB4/30/2020
APPL6/30/2020

 

Expected Output:-

Final:

ParentMonth
FB4/30/2020
FB5/31/2020
FB6/30/2020
FB7/31/2020
APPL6/30/2020
APPL7/31/2020

 

Any assistance would be helpful, thank you.

Labels (3)
1 Solution

Accepted Solutions
vivek_gajjar01
Partner
Partner

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;

View solution in original post

3 Replies
vivek_gajjar01
Partner
Partner

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;

View solution in original post

siddharth_kulka
Creator II
Creator II
Author

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!

siddharth_kulka
Creator II
Creator II
Author

Works perfectly, thanks a lot.