Loop through records to add month end date

edwardgoss — Mon, 20 Nov 2023 23:47:13 GMT

Hello,

I need to create a new table from an existing table. It needs to have a month end date for every month from the start of a record opening until it's closed. See below.

Table

Rec Number	Opened	Closed
5000	1/1/2023	5/11/2023

New Table

Rec Number	Opened	Closed	MonthEnd
5000	1/1/2023		1/31/2023
5000	1/1/2023		2/23/2023
5000	1/1/2023		3/31/2023
5000	1/1/2023		4/30/2023
5000	1/1/2023	5/11/2023	5/31/2023

The goal is to create a P50 and P90 Aging balance by month. This is the percentile value of the aging of the balance items. P90 would be 90% of the items have an age at or below the p90 value for a given month. Having the month end will allow me to calculate the days it was open as of the end of that month. There may be a better way to do this, so any recommendations would be appreciated.

Re: Loop through records to add month end date

Aasir — Tue, 21 Nov 2023 03:00:21 GMT

// Original Table
OriginalTable:
LOAD * INLINE [
RecNumber, Opened, Closed
5000, 1/1/2023, 5/11/2023
];

// New Table
NewTable:
LOAD
RecNumber,
Opened,
Closed,
MonthEnd
RESIDENT OriginalTable;

// Loop through months and generate MonthEnd values
FOR i = 0 TO DateDiff('M', Num(peek('Opened')), Num(peek('Closed')))
LET vMonthEnd = Date(MonthEnd(AddMonths(Num(peek('Opened')), i)), 'MM/DD/YYYY');
IF vMonthEnd > Num(peek('Closed')) THEN
EXIT FOR; // Exit loop if we go beyond the Closed date
ENDIF
NOCONCATENATE
LOAD
RecNumber,
Opened,
Closed,
'$(vMonthEnd)' AS MonthEnd
RESIDENT OriginalTable;
NEXT

DROP TABLE OriginalTable; // Drop the original table if not needed

topic Loop through records to add month end date in App Development

Loop through records to add month end date

Re: Loop through records to add month end date