topic Re: replacing nulls in a column with previously occurring value in QlikView

replacing nulls in a column with previously occurring value

Mon, 10 Nov 2014 19:27:54 GMT

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

Re: replacing nulls in a column with previously occurring value

maxgro — Mon, 10 Nov 2014 19:41:13 GMT

RESULT

SCRIPT

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

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

Date,

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

Resident a

order by Date

;

DROP Table a;

Re: replacing nulls in a column with previously occurring value

MarcoWedel — Mon, 10 Nov 2014 20:06:13 GMT

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

Re: replacing nulls in a column with previously occurring value

its_anandrjs — Mon, 10 Nov 2014 20:07:59 GMT

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