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

Retrieve non null value

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

7 Replies
SunilChauhan
Champion
Champion

select a, b

from aaa

where isnull(a)=0;

-1 for true

and 0 for false

hope this helps

Sunil Chauhan
Not applicable
Author

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)

)

sebastiandperei
Specialist
Specialist

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?

Not applicable
Author

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

chematos
Specialist II
Specialist II

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

v_iyyappan
Specialist
Specialist

Hi

     Use Script level

    

Load

      a,

      b

from Datasource

where

a <> '';

In Expression level

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

Regards,

Iyyappan

Not applicable
Author

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