Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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