Repeat Values Loading

[vinicius_siquei]

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

[maxgro · Accepted solution]

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;

[#3]

Hi,

Try using resident load where u can put a condition on the field to replace Null !

Cheers !

[vinicius_siquei]

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 ?

[#5]

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;

[vinicius_siquei]

I was doing something wrong Massimo. Thanks.

[maxgro]

I get this with peek, isn'it the expected result?

[vinicius_siquei]

You're right, I was doing something wrong.