topic Re: Refill fields in QlikView

Refill fields

tincholiver — Wed, 05 Jan 2022 13:52:54 GMT

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

Re: Refill fields

Or — Wed, 05 Jan 2022 14:10:24 GMT

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/InterRecordFunctions/Previous.htm

Re: Refill fields

tincholiver — Wed, 05 Jan 2022 15:52:22 GMT

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.

Re: Refill fields

rwunderlich — Thu, 06 Jan 2022 04:33:51 GMT

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

Re: Refill fields

tincholiver — Thu, 06 Jan 2022 13:52:26 GMT

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

Re: Refill fields

rwunderlich — Thu, 06 Jan 2022 16:07:41 GMT

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

Re: Refill fields

tincholiver — Thu, 06 Jan 2022 17:33:11 GMT

As always works perfectly

Thank you very much Rob.

Greetings