Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
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;
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;
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;