Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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
chauhans85
Esteemed Contributor

Re: Retrieve non null value

select a, b

from aaa

where isnull(a)=0;

-1 for true

and 0 for false

hope this helps

Not applicable

Re: Retrieve non null value

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
Valued Contributor

Re: Retrieve non null value

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

Retrieve non null value

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

chematos
Valued Contributor II

Re: Retrieve non null value

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
Valued Contributor

Re: Retrieve non null value

Hi

     Use Script level

    

Load

      a,

      b

from Datasource

where

a <> '';

In Expression level

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

Regards,

Iyyappan

Not applicable

Re: Retrieve non null value

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

Community Browser