Populate column with previous available value.

[micheledenardi]

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 ?

[Kushal_Chawda · Accepted solution]

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;

[sunny_talwar]

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;