generating missing months

[rcandeo]

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

[antoniotiman]

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 · Accepted solution]

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

]

[rcandeo]

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

[rcandeo]

thank you so much

[shetty_1]

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  🙂