Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;
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.