Populate missing Months in between and peek the previous month's value for missing

[prathipsrinivas]

Hi Qlikers,

I have an interesting requirement where in I need to populate between missing months and also get the value of previous month's for the missing month.

Let's say, I have a table like this

Month, Amount

Jan, 85

Mar, 76

Oct,90

And My desired output would have to be like this

Month, Amount

Jan, 85

Feb,85

Mar, 76,

Apr,76

May,76

Jun,76

July,76

Aug,76

Sep,76

Oct,90

Could you please help me how to achieve the same.

Thanks,

Prathip

[Taoufiq_Zarra · Accepted solution]

@prathipsrinivas  One solution:

Input:

load Date#('01/'&Month&'/2020','DD/MMM/YYYY') as Month,Amount 

inline [
Month, Amount
Jan, 85
Mar, 76
Oct,90
] ;

Tmp:
noconcatenate
load *,if(rowno()=1,Addmonths(Month,1),peek(Month)) as Start resident Input order by Month DESC;
 drop table Input;

Final:
noconcatenate
load Amount, month(monthstart(Month,iterno()-1)) as Month  resident Tmp  while monthstart(Month,iterno()-1)< Start; 


drop table Tmp;

to change inline [
Month, Amount
Jan, 85
Mar, 76
Oct,90
] ;

by From your database..

output: