topic Re: How to insert month in date intervals ? in QlikView

How to insert month in date intervals ?

ArnaudTML — Sat, 16 Nov 2024 04:14:08 GMT

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 !

Re: How to insert month in date intervals ?

sunny_talwar — Fri, 08 Mar 2019 12:35:44 GMT

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;

Re: How to insert month in date intervals ?

ArnaudTML — Fri, 08 Mar 2019 13:56:29 GMT

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)...

Re: How to insert month in date intervals ?

sunny_talwar — Fri, 08 Mar 2019 14:53:06 GMT

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;