topic If null(), get last value that had value in Visualization and Usability

If null(), get last value that had value

RafaelTorquato — Wed, 19 Apr 2023 18:27:46 GMT

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?

Re: If null(), get last value that had value

mfchmielowski — Wed, 19 Apr 2023 18:38:50 GMT

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.

Re: If null(), get last value that had value

RafaelTorquato — Wed, 19 Apr 2023 20:42:02 GMT

Unfortunately It's doesn't work

Re: If null(), get last value that had value

stevedark — Wed, 19 Apr 2023 22:07:32 GMT

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/