Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: 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  🙂