Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Everyone,
I'm trying to Populate missing values of the dates in between with peek function, i used the following code but still i couldn't get the results i'm looking for :
Table:
LOAD * INLINE [
Date, Article, Quantity
01/02/2021, A, 10
03/02/2021, A, 20
06/02/2021, B, 30
07/02/2021, C, 40
];
Join(Table)
LOAD
Date(MinDate+IterNO ()-1) as Date
While(MinDate+IterNO ()-1) <= Num(MaxDate -1);
LOAD
Min(Date) as MinDate,
Max(Date) As MaxDate
Resident Table;
Join(Table)
Load
If( Len(Quantity) = 0, peek('newQuantity'),Quantity) as newQuantity
Resident Table;
I attached my file, any help is much appreciated. Thank you !!
Try like attached file
script:
Table:
LOAD * INLINE [
Date, Article, Quantity
01/02/2021, A, 10
03/02/2021, A, 20
06/02/2021, B, 30
07/02/2021, C, 40
];
DateT:
LOAD
Date(MinDate+IterNO ()-1) as Date
While(MinDate+IterNO ()-1) <= Num(MaxDate);
LOAD
Min(Date) as MinDate,
Max(Date) As MaxDate
Resident Table;
T1:
Load Date as StartDate, Article, Quantity, Date(Alt(Peek(StartDate)-1, Today())) as EndDate Resident Table Order by Date desc;
DROP Table Table;
IntervalMatch(Date)
LOAD StartDate, EndDate Resident T1;
Join
LOAD * Resident T1;
DROP Table T1, DateT;
1. Be sure to sort the data in your final load in date order
Try like attached file
script:
Table:
LOAD * INLINE [
Date, Article, Quantity
01/02/2021, A, 10
03/02/2021, A, 20
06/02/2021, B, 30
07/02/2021, C, 40
];
DateT:
LOAD
Date(MinDate+IterNO ()-1) as Date
While(MinDate+IterNO ()-1) <= Num(MaxDate);
LOAD
Min(Date) as MinDate,
Max(Date) As MaxDate
Resident Table;
T1:
Load Date as StartDate, Article, Quantity, Date(Alt(Peek(StartDate)-1, Today())) as EndDate Resident Table Order by Date desc;
DROP Table Table;
IntervalMatch(Date)
LOAD StartDate, EndDate Resident T1;
Join
LOAD * Resident T1;
DROP Table T1, DateT;
Thank you so much for your help