Discussion Board for collaboration related to QlikView App Development.
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).
Any assistance would be helpful, thank you.
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 DISTINCTmin(Month) as MinMonthFROM ...DataTableSource;
let vMaxdate = num(monthend(today()));let vMindate = num(peek('MinMonth',0,'tempMonths'));
TRACE Min Month: $(vMindate);
Data:LOAD Parent,MonthFROM ...DataTableSource;
Datefield:LOAD DISTINCT MonthEnd(Datefield) as MonthEnd;LOAD date($(vMindate)+IterNo()-1) AS DatefieldAUTOGENERATE (1)WHILE $(vMindate)+IterNo()-1<= $(vMaxdate);
FinalData:LOAD *Resident DataWHERE MonthEnd>=Month;
Drop Tables Data, tempMonths;
View solution in original post
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!
Works perfectly, thanks a lot.