Fill missing values

[sfloberg]

I have a problem with getting the right values into the dates.

If there's only one missing Date (2017-08-04) the Value should be the next Value (30).

If several Dates are missing I want to take the previous Value (2017-08-07) 30 and the next Value (2017-08-11) 25. 30+25/2 and fill the rows 2017-08-08 to 2017-08-10 with the Value 27,5. Now I get 25 on each row.

The Excel (Missing dates 207-08-04, 2017-08-08 to 2017-08-10):

Script:

tmp:

LOAD Article, Store, Value, Date

FROM [MyTest.xlsx] (ooxml, embedded labels, table is Sheet1);

tmp_1:

load

Article,

Store,

Value,

Date,

RecNo() as rad,

if(Article = Previous(Article) and Store = Previous(Store), peek('Date'), date(Date-1) )as Previous_date,

if(Article = Previous(Article) and Store = Previous(Store), Date - peek('Date'),1 )as DaysBetween

Resident tmp Order by Article, Date, Date;

DROP Table tmp;

tmp_2:

load

*,

date(Previous_date+IterNo()) as NewDate

Resident tmp_1 While Previous_date+IterNo()<= Date;

DROP Table tmp_1;

Loaded script:

[antoniotiman]

Hi Stephanie,

may be this

Temp:
LOAD * Inline [
Article,Store,Value,Date
1,a,10,2017-08-01
1,a,20,2017-08-02
1,a,15,2017-08-03
1,a,30,2017-08-05
1,a,5,2017-08-06
1,a,30,2017-08-07
1,a,25,2017-08-11
1,a,2,2017-08-12
1,a,37,2017-08-13
1,a,52,2017-08-14
1,a,50,2017-08-15
];
Temp1:
LOAD *,
If(Article=Previous(Article) and Store=Previous(Store),
If(Previous(Date)-Date <> 1,If(Previous(Date)-Date = 2,Previous(Value),RangeSum(Previous(Value),Value)/2),0),0) as Value1,
If(Article=Previous(Article) and Store=Previous(Store),Previous(Date),Date+1) as Date1
Resident Temp
Order By Article,Store,Date Desc;
Drop Table Temp;
LOAD Article,Store,If(IterNo()=1,Value,Value1) as Value,Date(Date+IterNo()-1) as Date
Resident Temp1
While Date+IterNo()-1 < Date1;
Drop Table Temp1;

Regards,

Antonio