replacing nulls in a column with previously occurring value

[#1]

Hello,

  I have a table sorted by date that has sporadically occurring data in a field that I need to populate with the most recent value I can find.

I need to turn something like this:

Date, Value

1/1, A

1/2, -

1/3, -

1/4, B

1/5, -

1/6, -

1/7, -

1/8, C

1/9, -

Into this:

Date, Value

1/1, A

1/2, A

1/3, A

1/4, B

1/5, B

1/6, B

1/7, B

1/8, C

1/9, C

Is there a function or simple way to do this?

Thanks,

Tyler

[maxgro · Accepted solution]

RESULT

SCRIPT

Replace '-' with a test for null (ie len(trim(Value))=0)

a:

load * inline [

Date, Value

1/1, A

1/2, -

1/3, -

1/4, B

1/5, -

1/6, -

1/7, -

1/8, C

1/9, -

];

b:

NoConcatenate load

Date,

if(Value = '-', peek(Value), Value) as Value

Resident a

order by Date

;

DROP Table a;

[MarcoWedel]

Hi,

I also suspect that with '-' you're referring to the representation of null values, so one solution could be to test for Len(Value)=0 and create the missing values in the first place instead of adding them after the table has been loaded:

LOAD Date,
    If(Len(Value)=0,Peek(Value),Value) as Value
Inline [
Date, Value
1/1, A
1/2
1/3
1/4, B
1/5
1/6
1/7
1/8, C
1/9
];

Instead of the condition

Len(Value)=0

you could also test

IsNull(Value)

Value=''

or even combinations of these conditions:

Len(Value)=0 or IsNull(Value) or Value=''

hope this helps

regards

Marco

[its_anandrjs]

Hi,

You can try this way also by replace ( '-' )  with NULL string because some time symbol not identify then better to replace them or we can use the Isnull( ) function with the field. Or you can try this ways

TabA:

LOAD Date, Replace(Value,'-','Null') as Value;

LOAD * Inline

[ Date, Value

1/1, A

1/2, -

1/3, -

1/4, B

1/5, -

1/6, -

1/7, -

1/8, C

1/9, - ];

NoConcatenate

LOAD

*,

if(Value = 'Null',Peek(NewFlag),Value) as NewFlag

Resident TabA;

DROP Table TabA;

Regards

Anand