Creating month end dates till latest month

[siddharth_kulka]

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.

[vivek_gajjar01 · Accepted solution]

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;

[siddharth_kulka]

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]

Works perfectly, thanks a lot.