Looping Months based on number of future Months

[ChrisEvans]

Hi,

Need some assistance with looping based on number of months in the future. Here is a sample of the current data

TRX_ID	START	MONTHS	TRX FEE	MONTHLY FEE
558845	2023-01-01 00:00:00.000	3	1800	600
564613	2022-12-01 00:00:00.000	5	3250	650
465768	2022-12-01 00:00:00.000	4	2220	555

What we are looking to do is to allocate the monthly fee to the relevant months is belongs in as per below:

TRX_ID	DATE	FEE
558845	01 Jan 23	600
558845	01 Feb 23	600
558845	04 Mar 23	600
564613	01 Dec 22	650
564613	01 Jan 23	650
564613	01 Feb 23	650
564613	04 Mar 23	650
564613	04 Apr 23	650
465768	01 Dec 22	555
465768	01 Jan 23	555
465768	01 Feb 23	555
465768	04 Mar 23	555

[Or · Accepted solution]

Luckily, this should be pretty simple to do with a While keyword. Note that I'm not sure why you've used the first of the month in some cases and the fourth in others, so that part is not included.

Load TRX_ID, Date(AddMonths(Date#(START,'YYYY-MM-DD'),IterNo()-1),'YYYY-MM-DD') as Date, [MONTHLY FEE] as FEE INLINE [
TRX_ID, START, MONTHS, TRX FEE, MONTHLY FEE
558845, 2023-01-01, 3, 1800, 600
564613, 2022-12-01, 5, 3250, 650
465768, 2022-12-01, 4, 2220, 555 ]
While IterNo()<= MONTHS;