Fill in missing value with next available

[#1]

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

[swuehl · Accepted solution]

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;

[#3]

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

[#4]

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;

[swuehl]

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.

[#6]

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;