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
Did you mean:
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
MVP

Like this?

*,

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

]

5 Replies
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
Resident Temp1
While AddMonths(YearMonth,IterNo()-1) < Date(EndDate);
Drop Table
Temp1;

Refards,

Antonio

MVP

Like this?

*,

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

]

Creator III
Author

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

Creator III
Author

thank you so much

Partner - Contributor II

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  🙂

Community Browser