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

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

prathipsrinivas — Sat, 16 Nov 2024 17:54:55 GMT

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

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

Taoufiq_Zarra — Tue, 27 Oct 2020 14:19:54 GMT

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