Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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
can u try like : if(len(trim("ID Statement"))=0,'null conditon','not null conditon')
not working. Still the same. I am, very much surprised why this simple thing is not working.
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
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.
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:
This issue stands resolved now
Regards.
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.
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).