Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
edwardgoss
Contributor II

Loop through records to add month end date

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. 

Labels (1)
1 Reply
Aasir
Creator III

// 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