If null(), get last value that had value

[RafaelTorquato]

I have this data where some months I will have value for my item and other times I won't.
 I need that if it has no value that month, take the value of the last month that I had value:

LOAD
"Cód. Estabelecimento" as Estab,
"Cód. Produto" as Item,
SubField(replace("val-unit-ggf-m",'.',','),';',1) as GGF,
"Período Preço Item" as Date
FROM

How can I do this?

[mfchmielowski]

noconcatenate
load *, if(isNull(Date), previous(Date),Date) as nDate resident sourceTable order by Item, Date;
drop table sourceTable;

drop field Date;
rename field nDate to Date;

Check something like this.

[RafaelTorquato]

Unfortunately It's doesn't work

[stevedark]

Hi Rafael,

You will need to load the data with a specific sort order, by Establishment, Item and Date and then use the PEEK function to look back at the previous row.

In order to use the ORDER BY  you may need to do a RESIDENT load, something like:

NOCONCATENATE LOAD
  Estab,
  Item,
  Date,
  if(Estab = peek(Estab, -1) and Item = peek(Item, -1) and IsNull(GGF),
     peek(GGF, -1), GGF) as GGF
RESIDENT tmpDataTable
ORDER BY Estab, Item, Date;

DROP TABLE tmpDataTable;

This will only work if there is only one row per date for each Estab and Item. I'm guessing that might not be the case with the SubField going on, so you might need to also do a group by.

Hopefully this will help get you started in the right direction though.

Steve

https://www.quickintelligence.co.uk/blog/