Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
tincholiver
Creator III
Creator III

Refill fields

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  

 

 

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

 

View solution in original post

6 Replies
tincholiver
Creator III
Creator III
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Creator III
Creator III
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Creator III
Creator III
Author

As always works perfectly

Thank you very much Rob.

 

Greetings