topic Re: Populate column with previous available value. in QlikView

Populate column with previous available value.

micheledenardi — Tue, 01 Aug 2017 11:44:41 GMT

Good afternoon expert,

i have a table with some values = NULL (FROM table) and i want to populate these null values with the previous available value for that column, in order to obtain the expected result (TO table).

For example on 29/07/2017 and 30/07/2017 the previous available value for AUD is -1,4732 so i want to put these two null values with -1,4732... and so on...

How can i achieve this goal using an easy (or not so complex) script ?

Re: Populate column with previous available value.

Kushal_Chawda — Tue, 01 Aug 2017 11:50:09 GMT

Data:

LOAD *

FROM Excel:

Final:

noconcatenate

LOAD *,

if(len(trim(AUD))>0,AUD,peek(AUD_NEW)) as AUD_NEW,

if(len(trim(BRL))>0,BRL,peek(BRL_NEW)) as BRL_NEW,

...

Resident Data

order by Date;

drop table Data;

Re: Populate column with previous available value.

sunny_talwar — Tue, 01 Aug 2017 11:51:51 GMT

Something like this

Table:

LOAD Date,

AUD,

BRL,

CAD,

CHF,

CNY

FROM

[..\..\Downloads\ExampleData.xlsx]

(ooxml, embedded labels, table is Foglio1);

FinalTable:

LOAD Date,

If(Len(Trim(PurgeChar(AUD, '-'))) = 0, Peek('New_AUD'), AUD) as New_AUD,

If(Len(Trim(PurgeChar(BRL, '-'))) = 0, Peek('New_BRL'), BRL) as New_BRL,

If(Len(Trim(PurgeChar(CAD, '-'))) = 0, Peek('New_CAD'), CAD) as New_CAD,

If(Len(Trim(PurgeChar(CHF, '-'))) = 0, Peek('New_CHF'), CHF) as New_CHF,

If(Len(Trim(PurgeChar(CNY, '-'))) = 0, Peek('New_CNY'), CNY) as New_CNY

Resident Table

Order By Date desc;

DROP Table Table;