New field with last non null value.

[hufernandes]

On qlik sense, with the following table:
[TESTE]:
LOAD *,
If(len(trim("REF"))= 0 or "REF"='NULL' or "REF"='-', Null(), "REF" ) as "CONSUMO_T0";

LOAD *
Inline ["AM_SUBS", "REF"
202201,
202202,
202203, 7
202204,
202205,
202206, 12
202207,
202208, ]
;

How can I load a new field on script called "CONSUMO_REF" where its value should be the last not null loaded. The resulting table should be the following:

AM_SUBS	CONSUMO_REF
202201
202202
202203	7
202204	7
202205	7
202206	12
202207	12
202208	12

[brunobertels · Accepted solution]

hi try thiis 

[TESTE]:
LOAD *,
If(len(trim("REF"))= 0 or "REF"='NULL' or "REF"='-', Null(), "REF" ) as "CONSUMO_T0",

If(len(trim("REF"))> 0 ,"REF", peek(CONSUMO_REF) )as "CONSUMO_REF";

LOAD *
Inline ["AM_SUBS", "REF"
202201,
202202,
202203, 7
202204,
202205,
202206, 12
202207,
202208, ]
;