Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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.
Hi, You can try this:
LOAD * FROM Task.qvd (qvd)
Where IsNull(Task);
Best Regards.
Tonial.
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
Just a small hint here: the expression Len(Trim($1))=0 covers both NULL and empty string.
- Ralf
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
Hi,
Please find the attached file. Hope it helps you
Based on the data you posted, this would work:
LOAD *
From TableName
Where Task = 'null' ;
Best,
Matt
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.
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