Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm loading a table that doesn't have data for all the rows, like below:
DateID, Rate
10, 0.15
11,
12, 0.17
13,
14,
15, 0.38
For the rows that Column Rate is null, I must repeat last valid value.
I tried to do this by using previous command, However it doesn't works in cases when I have 2 null records in sequence.
The expected result is:
DateID, Rate
10, 0.15
11, 0.15
12, 0.17
13, 0.17
14, 0.17
15, 0.38
Any Ideias ?
Thanks
load
.....,
if(len(trim(Rate))=0, peek(Rate), Rate) as Rate
....
resident ...
order by DateID
a:
load * inline [
DateID, Rate
10, 0.15
11,
12, 0.17
13,
14,
15, 0.38
];
b:
NoConcatenate
load DateID, if(len(trim(Rate))=0, Peek(Rate), Rate) as Rate
Resident a;
drop table a;
load
.....,
if(len(trim(Rate))=0, peek(Rate), Rate) as Rate
....
resident ...
order by DateID
a:
load * inline [
DateID, Rate
10, 0.15
11,
12, 0.17
13,
14,
15, 0.38
];
b:
NoConcatenate
load DateID, if(len(trim(Rate))=0, Peek(Rate), Rate) as Rate
Resident a;
drop table a;
Hi,
Try using resident load where u can put a condition on the field to replace Null !
Cheers !
Thanks for Quick Answer Massimo.
Unfortunately, Peek as previous only returns the value for the rows that previous row has value.
By using peek I could inform the row parameter that could find n rows above a valid value.
However, I couldn't find a way to specify this parameter.
Do you have any idea ?
This code will work as long as you have the input data in the right order:
Table:
load * inline
[DateID, Rate
10, 0.15
11,
12, 0.17
13,
14,
15, 0.38];
Table2:
load
DateID,
Rate,
if(len(trim(Rate))>0, Rate, peek(NewRate)) as NewRate
Resident Table;
drop table Table;
I was doing something wrong Massimo. Thanks.
I get this with peek, isn'it the expected result?
You're right, I was doing something wrong.