topic peek and generate missing in QlikView

peek and generate missing

surajap123 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

My Source data doesn't have sales for all the months.Eg- The sales only happened in Jan and March.
The below script works to copy the sales of above month into the below month, which is fine.
Now my requirement is to populate sales for month Feb(Note that Feb month is never present in source data). So likewise sales should be propulated for all missing months in between. I am not getting clue how to achieve this.

Data:
LOAD *,Product & '|'& Month(Date) as key
INLINE [
Product, Date, Sales, Country
Pen, 01/01/2017, 10, UK
Pen, 02/01/2017, 10, UK
Pencil, 01/01/2017, 10, US
Pencil, 02/01/2017, 10, UK
Pen, 01/03/2017, 10, US
Pen, 02/03/2017, 10, UK
Pencil, 01/03/2017, , US
Pencil, 02/03/2017, , UK
];

AggrData:
LOAD Product & '|'& Month(Date) as key,
Product as Pd,
Month(Date) as Month,
if(sum(Sales)=0, If(Peek('Pd')= Product,Peek('AggrSales')),sum(Sales)) as AggrSales
Resident Data
Group by
Month(Date),Product
Order by Product, Date;

current ouput-

Expected ouput-

Please help!

Re: peek and generate missing

avinashelite — Tue, 28 Mar 2017 14:15:44 GMT

You don't have the date for FEB and hence its not showing

Re: peek and generate missing

avinashelite — Tue, 28 Mar 2017 14:22:01 GMT

If you want to generate the missing dates then you need to use calendar ..check this

Fill in missing dates till today

Generate missing Dates

Generating Missing Data In QlikView

Re: peek and generate missing

surajap123 — Wed, 29 Mar 2017 09:30:32 GMT

Hi Avinash,

I tried the below thread that you gave, but still struggling to achieve the result, as I am not sure how to generate calendar using month in my AggrData table.

Generate missing Dates

Please help!