How to insert month in date intervals ?

[ArnaudTML]

Hi,

I have a rate list with the month effect (in blue), and I'm looking for a list with every month and rates applied (in green).

Thank you !

[sunny_talwar]

Try this

Table:
LOAD * INLINE [
    SMIC_MonthEffect, SMIC_Rate
    01/01/2014, 9.53
    01/01/2015, 9.61
    01/01/2016, 9.67
    01/01/2017, 9.76
    01/01/2018, 9.88
    01/01/2019, 10.03
];

FinalTable:
LOAD MonthStart(SMIC_MonthEffect, IterNo() - 1) as SMIC_Month,
	 SMIC_Rate
While MonthStart(SMIC_MonthEffect, IterNo() - 1) < End;
LOAD SMIC_MonthEffect,
	 SMIC_Rate,
	 Date(Alt(Peek('SMIC_MonthEffect'), MonthEnd(Today()))) as End
Resident Table
Order By SMIC_MonthEffect desc;

DROP Table Table;

[ArnaudTML]

Thanks for your help but there is a problem : values are doubled as soon as we move on to the next year (in 2019, there are 6 different)...

[sunny_talwar · Accepted solution]

My bad, try this

Table:
LOAD * INLINE [
    SMIC_MonthEffect, SMIC_Rate
    01/01/2014, 9.53
    01/01/2015, 9.61
    01/01/2016, 9.67
    01/01/2017, 9.76
    01/01/2018, 9.88
    01/01/2019, 10.03
];

TempTable:
LOAD SMIC_MonthEffect,
	 SMIC_Rate,
	 Date(Alt(Peek('SMIC_MonthEffect'), MonthEnd(Today()))) as End
Resident Table
Order By SMIC_MonthEffect desc;

FinalTable:
LOAD MonthStart(SMIC_MonthEffect, IterNo() - 1) as SMIC_Month,
	 IterNo() as IterNo,
	 SMIC_MonthEffect,
	 SMIC_Rate,
	 End
Resident TempTable
While MonthStart(SMIC_MonthEffect, IterNo() - 1) < End;

DROP Tables Table, TempTable;