topic Re: Complet data in QlikView

Complet data

tincholiver — Sun, 17 Jan 2021 01:50:02 GMT

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

Re: Complet data

Saravanan_Desingh — Sun, 17 Jan 2021 05:14:56 GMT

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;

Re: Complet data

Saravanan_Desingh — Sun, 17 Jan 2021 05:15:26 GMT

Output:

Re: Complet data

yassinemhadhbi — Sun, 17 Jan 2021 15:42:23 GMT

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

Re: Complet data

tincholiver — Sun, 17 Jan 2021 17:53:38 GMT

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