Refill fields

[tincholiver]

I everyone, i´m tring to solve the following case.

I have a data series that contains empty empty fields, I need to fill them with the last available value of the series. I am attaching a sample of the table, thank you very much in advance.

My Data			Desired Data
Date	Value		Date	Value
04/01/2022	77.717.765		04/01/2022	77.717.765
03/01/2022			03/01/2022	76.760.265	I need to repeat the last available number
02/01/2022	76.760.265		02/01/2022	76.760.265
01/01/2022	76.629.499		01/01/2022	76.629.499
31/12/2021	76.606.972		31/12/2021	76.606.972
30/12/2021	76.524.614		30/12/2021	76.524.614
29/12/2021			29/12/2021	74.785.702	I need to repeat the last available number
28/12/2021			28/12/2021	74.785.702	I need to repeat the last available number
27/12/2021	74.785.702		27/12/2021	74.785.702
26/12/2021	74.713.629		26/12/2021	74.713.629
25/12/2021			25/12/2021	74.699.408	I need to repeat the last available number
24/12/2021	74.699.408		24/12/2021	74.699.408
23/12/2021	74.392.087		23/12/2021	74.392.087
22/12/2021	74.049.613		22/12/2021	74.049.613
21/12/2021			21/12/2021	73.701.996	I need to repeat the last available number
20/12/2021	73.701.996		20/12/2021	73.701.996
19/12/2021	73.401.337		19/12/2021	73.401.337
18/12/2021	73.285.642		18/12/2021	73.285.642
17/12/2021	73.110.975		17/12/2021	73.110.975
16/12/2021	72.812.317		16/12/2021	72.812.317
15/12/2021	72.500.924		15/12/2021	72.500.924
14/12/2021	72.130.749		14/12/2021	72.130.749
13/12/2021	71.799.106		13/12/2021	71.799.106
12/12/2021	71.507.682		12/12/2021	71.507.682
11/12/2021	71.397.860		11/12/2021	71.397.860
10/12/2021	71.219.249		10/12/2021	71.219.249
09/12/2021			09/12/2021	70.657.836	I need to repeat the last available number
08/12/2021	70.657.836		08/12/2021	70.657.836
07/12/2021	70.172.359		07/12/2021	70.172.359
06/12/2021	69.891.521		06/12/2021	69.891.521
05/12/2021			05/12/2021	69.780.773	I need to repeat the last available number
04/12/2021	69.780.773		04/12/2021	69.780.773
03/12/2021	69.596.446		03/12/2021	69.596.446
02/12/2021	69.269.878		02/12/2021	69.269.878
01/12/2021	68.874.746		01/12/2021	68.874.746

[Or]

Have a look at https://community.qlik.com/t5/QlikView-Documents/Generating-Missing-Data-In-QlikView/ta-p/1491394

In this case, it looks like a fairly simple case of using the Previous() function - https://help.qlik.com/en-US/qlikview/May2021/Subsystems/Client/Content/QV_QlikView/Scripting/InterRe...

[tincholiver]

I do not get the results:

in the first link it is resolved how to incorporate dates, in my case they are all (because have more data in each day)

in the second post the function previus () when not registering data in the field throws me zeros.

i´m tring with this post https://community.qlik.com/t5/QlikView-App-Dev/fill-out-missing-dates/td-p/1762804 but anything yet.

[rwunderlich]

I think you'll want to use the Peek() function. And you need to load the data in ascending Date order. 

Temp:
LOAD Date,
     Value
FROM
[https://community.qlik.com/t5/QlikView-App-Dev/Refill-fields/td-p/1877503]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1, filters(
Remove(Row, Pos(Top, 1))
));

Final:
NoConcatenate
LOAD
Date,
if(len(Value)=0, Peek('Value'), Value) as Value
Resident Temp
Order by Date
;
Drop Table Temp; 

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[tincholiver]

Rob, it worked perfectly !!
How would you do the same if we added a dimension, for example:

Date	Country	Value
05/01/2022	Argentina
05/01/2022	Australia	20488662
05/01/2022	Belgium
05/01/2022	China
04/01/2022	Argentina	38343044
04/01/2022	Australia	20475452
04/01/2022	Belgium	8914432
04/01/2022	China
03/01/2022	Argentina
03/01/2022	Australia	20463839
03/01/2022	Belgium	8913486
03/01/2022	China
02/01/2022	Argentina	38214609
02/01/2022	Australia	20460234
02/01/2022	Belgium	8911911
02/01/2022	China
01/01/2022	Argentina	38200181
01/01/2022	Australia	20444346
01/01/2022	Belgium	8911909
01/01/2022	China

[rwunderlich · Accepted solution]

Order by Country, Date.  Add a check that the previous row country = this country. 

Temp:
LOAD
Date,
     Country,
     Value
FROM
[https://community.qlik.com/t5/QlikView-App-Dev/Refill-fields/m-p/1877788]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @2);

Final:
NoConcatenate
LOAD
Date,
Country,
if(len(Value)=0 and Previous(Country) = Country, Peek('Value'), Value) as Value
Resident Temp
Order by Country, Date
;
Drop Table Temp; 

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[tincholiver]

As always works perfectly

Thank you very much Rob.

Greetings