topic Re: Select against a "null" condition in QlikView

Select against a "null" condition

Fri, 23 Aug 2013 20:47:11 GMT

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!

Re: Select against a "null" condition

calvindk — Fri, 23 Aug 2013 21:11:14 GMT

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.

Re: Select against a "null" condition

fernando_tonial — Fri, 23 Aug 2013 21:12:39 GMT

Hi, You can try this:

LOAD * FROM Task.qvd (qvd)

Where IsNull(Task);

Best Regards.

Tonial.

Re: Select against a "null" condition

matthieu_burel — Fri, 23 Aug 2013 21:37:03 GMT

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

Re: Select against a "null" condition

rbecher — Fri, 23 Aug 2013 22:23:45 GMT

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

- Ralf

Re: Select against a "null" condition

MayilVahanan — Sat, 24 Aug 2013 05:26:03 GMT

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

Re: Select against a "null" condition

Sat, 24 Aug 2013 05:38:16 GMT

Hi,

Please find the attached file. Hope it helps you

Re: Select against a "null" condition

Sat, 24 Aug 2013 16:14:36 GMT

Based on the data you posted, this would work:

LOAD *

From TableName
Where Task = 'null' ;

Best,

Matt

Re: Select against a "null" condition

matthieu_burel — Sat, 24 Aug 2013 16:58:21 GMT

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

Anonymous — Sun, 25 Aug 2013 10:02:46 GMT

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

Re: Select against a "null" condition

Fri, 30 Aug 2013 15:05:15 GMT

I want to thank to everyone that answer this post.

I used the switch "NullAsValue" / "NullAsNull", as Bill show in the last post. However, all of your answers was very helpfull for me; to understand how works QlikView.

Anders, Fernando, Matthieu, Ralf, Mayil, Amuthabharathi, Matthew, Bill: Thank you!!!!

Re: Select against a "null" condition

Fri, 30 Aug 2013 16:14:28 GMT

I do something like this:

LOAD

If(Len([Field1]) = 0, 0, [Field1]) as [Field1],

If(Len([Field2]) = 0, 0, [Field2]) as [Field2],

If(Len([Field3]) = 0, 0, [Field3]) as [Field3]

FROM

QVDs\myfile.qvd (qvd);

Re: Select against a "null" condition

Mon, 02 Sep 2013 05:03:19 GMT

Hi,

Matthieu thank you very much i will use it in future

Thanks and regards,

S.Amuthabharathi