Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
mansoorsheraz
Contributor III
Contributor III

If statement not working on the true side once dealing with NULL values

Hi,

I have met with a strange issue where by a straight forward IF condition is not working for me in the charts.  Below is a simple table:

mansoorsheraz_0-1640284661273.png

I am, trying to use the IF condition to show some text in the third column but its not working. I used it by using null as well using the length but didn't work in both the cases.  Here are the two IF statements:

if(IsNull("ID Statement"),'null conditon','not null conditon')


if(len("ID Statement") < 3,'null condition','not null condition')

I am, not so sure whats going wrong. Data is coming from multiple tables:

[child]:
LOAD
"Name Service",
Domain,
"ID Service"
FROM [lib://DataFiles/services.xlsx]
(ooxml, embedded labels, table is [services]);

[parent]:
LOAD
"Name Parent",
"ID Service"
FROM [lib://DataFiles/services.xlsx]
(ooxml, embedded labels, table is [services]);

LOAD
"ID Service",
"ID Statement",
len("ID Statement") as "LEN ID Statement"
FROM [lib://DataFiles/services.xlsx]
(ooxml, embedded labels, table is [sm statement doc]);

What am I doing wrong here ? Any help is appreciated.

Regards

Labels (4)
7 Replies
anat
Specialist II
Specialist II

can u try like :  if(len(trim("ID Statement"))=0,'null conditon','not null conditon')

mansoorsheraz
Contributor III
Contributor III
Author

not working. Still the same. I am, very much surprised why this simple thing is not working.

tresesco
MVP
MVP

Null in qlik (and probably in all BI tools) is a topic that needs proper understanding to deal with it correctly. The nulls that you are seeing here, are probably 'NULL values created as a result of the of field value combinations to be displayed in a table', that means they actually are not part of your data structure. Hence, you can't really catch them in expression at the front-end. Read and learn more about this 'neglected giant' here : 

https://community.qlik.com/t5/QlikView-Documents/NULL-handling-in-QlikView/ta-p/1484472

mansoorsheraz
Contributor III
Contributor III
Author

Yeah they are not part of the data, I agree to it, but how to catch it as I need to do some decision making on it. Secondly when in the dimension I uncheck "include null values" it dosesn't display the  rows with "-" so I thought may be I could handle it by checking for it as NULL.

mansoorsheraz
Contributor III
Contributor III
Author

Thanks a lot tresesco, the pdf helped me. In my only() solved the problem:

if(isNull(only("ID Statement")),'null conditon','not null conditon')

Here is the resultant table:

mansoorsheraz_0-1640337640357.png

This issue stands resolved now

Regards.

 

mansoorsheraz
Contributor III
Contributor III
Author

There is one issue though that this statement below is not working at the load time :

if(isNull(only("ID Statement")),'null conditon','not null conditon') as "ID STMT"

Can anyone help with this ? I am, getting an error.

tresesco
MVP
MVP

Only() is an aggregation function, hence to use this in the script load statement, you would need 'group by' clause. Also, be aware that, if you are using this statement, for the same problem in chart you stated above, it might not give the same result, because of the data combination differences (unless you are joining the tables).