Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

replacing nulls in a column with previously occurring value

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

1 Solution

Accepted Solutions
maxgro
MVP
MVP

RESULT

1.png

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;

View solution in original post

3 Replies
maxgro
MVP
MVP

RESULT

1.png

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

];

QlikCommunity_Thread_141526_Pic1.JPG.jpg

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;

Prevval.png

Regards

Anand