topic Re: Retrieve non null value in QlikView

Retrieve non null value

Wed, 18 Apr 2012 12:15:19 GMT

I need to retrieve data in specific column(s )that do not have any null value

in SQL server you would like to write

select a, b

from aaa

where a is not null

How would you retrieve non null value in the table in the expressen interface?

// JJ

Re: Retrieve non null value

SunilChauhan — Wed, 18 Apr 2012 12:19:19 GMT

select a, b

from aaa

where isnull(a)=0;

-1 for true

and 0 for false

hope this helps

Re: Retrieve non null value

Wed, 18 Apr 2012 12:46:59 GMT

If I understand from your point of view, the solution also can be used in this context below?

COUNT

(

IF(a = 1 AND ISNULL(a) = 0, 1, 0)

)

Re: Retrieve non null value

sebastiandperei — Wed, 18 Apr 2012 13:21:29 GMT

In Qlikview, you have (at least) 3 worlds: The Script World, The Expressions World and The Macro World.

There is few cases that the same "word" functions in two worlds at the same way.

So... What you want to do? Create a field in script or make a graphic expression?

Retrieve non null value

Wed, 18 Apr 2012 13:26:00 GMT

let's make it as both that is script and graphic expression.

Re: Retrieve non null value

chematos — Wed, 18 Apr 2012 15:41:53 GMT

There are a few ways to do this.

Normally using the function isnull() like said Sunil.

Other alternative is like this: if(Len(Trim(Field))>0)

Also in the script you could use: If(Field<>' ')

Regards,

Chema

Re: Retrieve non null value

v_iyyappan — Wed, 18 Apr 2012 17:24:18 GMT

Use Script level

Load

from Datasource

where

a <> '';

In Expression level

= if (IsNull(a),1,0)

Regards,

Iyyappan

Re: Retrieve non null value

Wed, 18 Apr 2012 18:23:40 GMT

Hi,

In qlikview also you can use quite similar syntax.

In load script you can write

load a, b

from aaa

where not isnull(a);

the same not isnull(a) can be used in charts also with if condition,

suppose you want sum of b when a is not null then,

sum(if(not isnull(a),b))

Also, as others said sometimes (when loading data from text files) isnull() may not work then you can use len() function. The reason for isnull() not working is that by definition text files can not contain null data.

Ashutosh