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
@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:
@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: