Fill empty values with last not null values

[Jsobrinho77]

Hi guys,

I have a table, with a column Regra that contains null values, and I need to complete it with the last values, I'm trying to use the code (https://community.qlik.com/t5/QlikView-App-Dev/Fill-empty-values-with-last-not-null-values/td-p/1310...)

try this

Data:

LOAD

  ID,

  Task,

  [Start Date],

  [End Date],

  [Workflow Id]

FROM Source;

Final:

noconcatenate

LOAD *,

          if(len(trim([Workflow Id]))=0,peek([Workflow ID]),[Workflow Id]) as [Workflow ID]

Resident Data

order by ID,Task,[Start Date];

My code:

SQL_Sales:
LOAD *,
if(len(trim(Regra))>0,Regra,peek(Regra)) as Value
Resident __SQL_Sales order by CreatedAt DESC;

But some lines still contain null, how can I solve this?

[vinieme12]

Thats because your source column itself is null! 

here is how peek() works, peek() will look at previous row in your dataset while loading

Date,Regra,Value
2022-04-12,6,6
2022-04-11,6,6
2022-04-10,6,6
2022-04-09,null,6   <---- Here regra is null, so peek(regra) returns the value for 2022-04-10
2022-04-08,null,null  <---- Here regra is null, so peek(regra) returns the value for 2022-04-09
2022-04-07,null,null  <---- Here regra is null, so peek(regra) returns the value for 2022-04-07
2022-04-06,null,null

you need to Refer to the value column instead

SQL_Sales:
LOAD *,
if(len(trim(Regra))>0,Regra,peek(Value)) as Value
Resident __SQL_Sales order by CreatedAt DESC;