Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi everyone,
I have a table with dates and prices in which I want to fill in the days without value with the previous value, for example:
my data
Date | Price |
01/01/2021 | 55,34 |
05/01/2021 | 56,79 |
06/01/2021 | 57,99 |
07/01/2021 | 59,25 |
08/01/2021 | 60,80 |
11/01/2021 | 61,99 |
12/01/2021 | 63,80 |
15/01/2021 | 64,40 |
desired data
Date | Price |
1/1/2021 | 55,34 |
2/1/2021 | 55,34 |
3/1/2021 | 55,34 |
4/1/2021 | 55,34 |
5/1/2021 | 56,79 |
6/1/2021 | 57,99 |
7/1/2021 | 59,25 |
8/1/2021 | 60,80 |
9/1/2021 | 60,80 |
10/1/2021 | 60,80 |
11/1/2021 | 61,99 |
12/1/2021 | 63,80 |
13/1/2021 | 63,80 |
14/1/2021 | 63,80 |
15/1/2021 | 64,40 |
thanks
One Solution is,
tab1:
LOAD *,Peek(tDate) As pDate, tDate-Peek(tDate) As Diff;
LOAD *,Date(Date#(Date,'DD/MM/YYYY')) As tDate;
LOAD * INLINE [
Date, Price
01/01/2021, "55,34"
05/01/2021, "56,79"
06/01/2021, "57,99"
07/01/2021, "59,25"
08/01/2021, "60,80"
11/01/2021, "61,99"
12/01/2021, "63,80"
15/01/2021, "64,40"
];
Join(tab1)
LOAD Date(pDate+IterNo()-1) As tDate
Resident tab1
While IterNo()<=Diff+1;
tab2:
NoConcatenate
LOAD tDate, If(IsNull(Price), Peek(Price), Price) As Price
Resident tab1
Order By tDate;
Drop Table tab1;
Output:
a:LOAD * INLINE [
Date,Price
01/01/2021,55,34
05/01/2021,56,79
06/01/2021,57,99
07/01/2021,59,25
08/01/2021,60,80
11/01/2021,61,99
12/01/2021,63,80
15/01/2021,64,40];
b:LOAD *,Previous(Date) as d
RESIDENT a
order by Date desc
;
drop table a;
NoConcatenate
table:LOAD * resident b order by Date asc;
drop table b;
NoConcatenate
Dates:
Load
Price,
Date( Date + IterNo() - 1 ) as Date
Resident table
While IterNo() <= d - Date ;
drop table table
Thanks to both, the solution that worked is Saran7de's while Yassinemhadhbi's
does not take the value of the last day:
that solution gives this result:
Date Price
01/01/2021 55
2/1/2021 55
3/1/2021 55
4/1/2021 55
05/01/2021 56
06/01/2021 57
07/01/2021 59
08/01/2021 60
9/1/2021 60
10/1/2021 60
11/01/2021 61
12/01/2021 63
13/1/2021 63
14/1/2021 63
Thank you very much too