Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
prathipsrinivas
Creator
Creator

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

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

1 Solution

Accepted Solutions
Taoufiq_Zarra
Master II
Master II

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

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

1 Reply
Taoufiq_Zarra
Master II
Master II

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

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post