Select against a "null" condition

[#1]

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!

[calvindk]

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.

[fernando_tonial]

Hi, You can try this:

LOAD * FROM Task.qvd (qvd)

Where IsNull(Task);

Best Regards.

Tonial.

[matthieu_burel]

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

[rbecher]

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

- Ralf

[MayilVahanan]

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

[#7]

Hi,

Please find the attached file. Hope it helps you

[#8]

Based on the data you posted, this would work:

LOAD *

From TableName
Where Task = 'null' ;

Best,

Matt

[matthieu_burel]

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.

[Anonymous · Accepted solution]

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