topic generating missing months in QlikView

generating missing months

rcandeo — Mon, 26 Jan 2026 18:19:17 GMT

I Know that has dozens of posts, but none of them solved my problem, so please help me with this?

consider that my table is similar like below:

Price_Table, YearMonth, amount

Table1, 2016/12, 500

Table2, 2017/05, 35

Table2, 2017/08, 45

I need to fill all missing months to each Price_Table till actual year/month

So in the abovel example I expect this result:

Table1, 2016/12, 500

Table1, 2017/01, 500

Table1, 2017/02, 500

Table1, 2017/03, 500

Table1, 2017/04, 500

Table1, 2017/05, 500

Table1, 2017/06, 500

Table1, 2017/07, 500

Table1, 2017/08, 500

Table1, 2017/09, 500

Table2, 2017/05, 35

Table2, 2017/06, 35

Table2, 2017/07, 35

Table2, 2017/08, 45

Table2, 2017/09, 45

Re: generating missing months

antoniotiman — Tue, 05 Sep 2017 04:28:52 GMT

Hi Robson,

Temp:
Load Price_Table,amount,Date(Date#(YearMonth,'YYYY/MM'),'YYYY/MM') as YearMonth Inline [
Price_Table,YearMonth, amount
Table1, 2016/12, 500
Table2, 2017/05, 35
Table2, 2017/08, 45];
Temp1:
NoConcatenate
LOAD *,If(Price_Table = Peek(Price_Table),Peek(YearMonth),Date(Today(),'YYYY/MM')) as EndDate
Resident Temp Order By Price_Table,YearMonth desc;
Drop Table Temp;
NoConcatenate
LOAD Price_Table,amount,Date(AddMonths(YearMonth,IterNo()-1),'YYYY/MM') as YearMonth
Resident Temp1
While AddMonths(YearMonth,IterNo()-1) < Date(EndDate);
Drop Table Temp1;

Refards,

Antonio

Re: generating missing months

tresB — Tue, 05 Sep 2017 04:38:47 GMT

Like this?

Load

Date(AddMonths(Date,IterNo()-1),'YYYY/M') as NewDate

While Today()>=AddMonths(Date, IterNo()-1);

Load *,

Date(Date#(YearMonth,'YYYY/M'),'YYYY/M')as Date,

SubField(YearMonth,'/',1) as Year,

SubField(YearMonth,'/',2) as Month Inline [

Price_Table, YearMonth, amount

Table1, 2016/12, 500

Table2, 2017/05, 35

Table2, 2017/08, 45

]

Re: generating missing months

rcandeo — Wed, 06 Sep 2017 21:25:00 GMT

Sorry my late. I probably made some mistake, but I did not was able to work with this code

Re: generating missing months

rcandeo — Wed, 06 Sep 2017 21:25:43 GMT

thank you so much

Re: generating missing months

shetty_1 — Sun, 16 Feb 2020 07:16:44 GMT

Hi,

My question is similar to this but needs different output

Data:

Material No	month year	Value
10000111	Sep2018	3750
10000111	Oct2018	1500
10000111	Apr2019	0
10000111	Oct2019	10987

Expected Output:

Material No	month year	Value
10000111	Sep2018	3750
10000111	Oct2018	1500
10000111	Nov2018	1500
10000111	Dec2018	1500
10000111	Jan2019	1500
10000111	Feb2019	1500
10000111	Mar2019	1500
10000111	Apr2019	0
10000111	May 2019	0
10000111	June 2019	0
10000111	July 2019	0
10000111	Aug 2019	0
10000111	Sep 2019	0
10000111	Oct 2019	10987
10000111	Nov 2019	10987
10000111	Dec 2019	10987

I need the above output

Thanks in Advance 🙂