Populating missing values

[WalidChoukri]

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 !!

[Dalton_Ruer]

1. Be sure to sort the data in your final load in date order 

[MayilVahanan · Accepted solution]

Hi @WalidChoukri 

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;

[WalidChoukri]

Thank you so much for your help

[MayilVahanan]

Welcome. Happy to help you