Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Select against a "null" condition

Hello.  I'm a newbie in QlikView, and need a little help:

I have the following table:

ID_Workflow  Phase  Task  Asigned

1001         01     null    Carl

1001         01     01      John

1001         01     02      Samantha


I need to do the equivalent to "SELECT * FROM <table> WHERE Task is NULL"


What is the equivalent syntax in QlikView?  I need to do in the load script, or I can do it in a Chart ?


Thank you!





1 Solution

Accepted Solutions

Re: Select against a "null" condition

Dario

Assuming your value actually is null and not a text field containing the letters 'null', then you can use the NullasValue function to set the true null to a text string

     SET NullValue = '<Null>';

     NullAsValue *;

     load blah.... from blah..... ;

     NullAsNull *;

Matt Fryer gives a good explanation of handling nulls, see QlikView Addict: Handling Nulls in QlikView

Best Regards,  Bill

View solution in original post

12 Replies
Highlighted
calvindk
Contributor III

Re: Select against a "null" condition

You cannot select nulls in qlikview, however you can select excluded, possible, not possible, etc

That being said, if possible, convert your nulls to something like "MissingValue" in script, as that is by far the easiest to work with.

Partner
Partner

Re: Select against a "null" condition

Hi, You can try this:

LOAD * FROM Task.qvd (qvd)

Where IsNull(Task);

Best Regards.

Tonial.

Luminary
Luminary

Re: Select against a "null" condition

I advise you to do it in the script : Null value can't be a selected element in the restitution.

So, in the script, declare a variable :

SET F_FieldQuality = IF(IsNull($1) = -1 OR Trim($1) = '', 'EMPTY', Trim($1))


and use the following syntax in the LOAD script to replace / correct the field data :

LOAD $(F_FieldQuality(MYFIELD_BEFORE)) as MYFIELD_AFTER,

It will simplify your code and offer you the ability to select the EMPTY value.

Regards

Matthieu

MVP & Luminary
MVP & Luminary

Re: Select against a "null" condition

Just a small hint here: the expression Len(Trim($1))=0 covers both NULL and empty string.

- Ralf

Re: Select against a "null" condition

Hi

Try with

Load * from tablename where Len(Trim(Task)) = 0;

Trim() - function remove extra space.

Len() - function count the length of the value

Hope it helps

Not applicable

Re: Select against a "null" condition

Hi,

Please find the attached file. Hope it helps you

Not applicable

Re: Select against a "null" condition

Based on the data you posted, this would work:

LOAD *

From TableName
Where Task = 'null' ;

Best,

Matt

Luminary
Luminary

Re: Select against a "null" condition

Be careful with you data

Null and Null() are not the same thing : the first is the text value 'null' while null() is the empty value.

So use Task = 'null' or IsNull(Task) = -1 depending what you have in your real case.

Re: Select against a "null" condition

Dario

Assuming your value actually is null and not a text field containing the letters 'null', then you can use the NullasValue function to set the true null to a text string

     SET NullValue = '<Null>';

     NullAsValue *;

     load blah.... from blah..... ;

     NullAsNull *;

Matt Fryer gives a good explanation of handling nulls, see QlikView Addict: Handling Nulls in QlikView

Best Regards,  Bill

View solution in original post