Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rcandeo
Creator III
Creator III

generating missing months

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

1 Solution

Accepted Solutions
tresesco
MVP
MVP

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

]

Capture2.PNG

View solution in original post

5 Replies
antoniotiman
Master III
Master III

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

tresesco
MVP
MVP

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

]

Capture2.PNG

rcandeo
Creator III
Creator III
Author

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

rcandeo
Creator III
Creator III
Author

thank you so much

shetty_1
Partner - Contributor II
Partner - Contributor II

Hi,

My question is similar to this but needs different output

Data:

Material No month yearValue 
10000111Sep20183750 
10000111Oct20181500 
10000111Apr2019

0

 
10000111Oct201910987 

 

Expected Output:

Material Nomonth yearValue
10000111Sep20183750
10000111Oct20181500
10000111Nov20181500
10000111Dec20181500
10000111Jan20191500
10000111Feb20191500
10000111Mar20191500
10000111Apr20190
10000111May 20190
10000111June 20190
10000111July 20190
10000111Aug 20190
10000111Sep 20190
10000111Oct 201910987
10000111Nov 201910987
10000111Dec 201910987

 

I need the above output 

Thanks in Advance  🙂