topic Fill in missing value with next available in QlikView

Fill in missing value with next available

Fri, 16 Dec 2011 20:24:42 GMT

Hello All,

I have hit a bit a of a bump in the road and I can't seem to figure out the solution. Here is my issue. I have a table with 6 different months in the data with one record for each month. I want to populate all 12 months with data using the data from the next available month for the missing month.

Ex.

This is my table:

month, value

2, 10

4,15

6,20

7,25

9,30

12,35

This is what I need:

month,value

1,10

2,10

3,15

4,15

5,20

6,20

7,25

8,30

9,30

10,35

11,35

12,35

Thank you all in advance,

Marc

Fill in missing value with next available

swuehl — Fri, 16 Dec 2011 20:31:51 GMT

Marc,

maybe this does fit your needs:

INPUT:

LOAD * INLINE [

month, value

2, 10

4,15

6,20

7,25

9,30

12,35

];

right join LOAD recno() as month autogenerate 12;

RESULT:

noconcatenate load

month,

if(isnull(value), rangesum(peek(value)), value) as value

resident INPUT order by month desc;

drop table INPUT;

Fill in missing value with next available

Fri, 16 Dec 2011 20:49:45 GMT

I now have a table with all 12 months but the missing values are all zeroes.

Fill in missing value with next available

Fri, 16 Dec 2011 20:52:49 GMT

It must be something I am doing trying to convert the simple example to my code. Here is my code:

Market:

LOAD Date as MarketDate,

Delivery as Month,

month(Delivery) as month,

Price as market

FROM

(ooxml, embedded labels, table is Market);

right join LOAD recno() as month autogenerate 12;

RESULT:

noconcatenate load

month,

if(isnull(market), rangesum(peek(market)), market) as value

resident Market order by month desc;

Fill in missing value with next available

swuehl — Fri, 16 Dec 2011 21:10:42 GMT

So if you comment the last RESULT table load out, what do you get in detail for table Market? Could you post it here? Just asking myself if you may need to use num(month(Delivery)) as month in your first load..

If all works sometime, then you probably want to load also MarketDate and Month (btw. is Delivery a date or a month?) in your final load and drop the MARKET table.

Fill in missing value with next available

Fri, 16 Dec 2011 21:15:20 GMT

I figured out where I went wrong. Thank you so much for your help. Here is the corrected code:

Market:

LOAD Date as MarketDate,

Commodity,

Delivery as Month,

num(month(Delivery)) as month,

Price as market

FROM

(ooxml, embedded labels, table is Market);

right join

LOAD recno() as month

autogenerate 12;

RESULT:

noconcatenate load

MarketDate,

Commodity,

Month,

month,

if(isnull(market), rangesum(peek(market)), market) as market

resident Market order by month desc;

drop table Market;