Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am a bit confused about a set analysis expression that does not work as expected when the fields in a table are empty. Here a sample script:
LET vThisYear=Year(Today());
Employees:
LOAD
*
INLINE
[
ID,Name,Salary
100,Person A, 110000
200,Person B, 120000
300,Person C, 130000
400,Person D, 140000
500,Person E, 150000
]
(DELIMITER IS ',');
Hired:
LOAD
*
INLINE
[
ID,Hired
300,2017
400,2017
500,2018
]
(DELIMITER IS ',');
If I use the expression: Count({<Hired={$(vThisYear)}>}ID) in a KPI object, it correctly returns 1.
However, if the Hired table in the script is empty (which might happen) like this:
Hired:
LOAD
*
INLINE
[
ID,Hired
]
(DELIMITER IS ',');
the same KPI object would return 5 instead of 0.
My questions are:
A) Why does this happen?
B) How to handle this so that the expression returns 0 when the table is empty?
Thank you!
B) Use this expression: Count({<Hired={$(vThisYear)}>}Hired) It works.
A) Maybe your new table without any data don´t add any informatión to the ID dimension. Then the Count is the Total.
Add a record with Null values to the column, The expression will work.
Hired:
LOAD
*
INLINE
[
ID,Hired
Null(),Null()
]
(DELIMITER IS ',');
Hi and thanks for responding!
Yes, it will work even if I put a single comma there.
However, this is an extremely simplified example of a much larger data model and some of the tables come from databases that I cannot control or edit. That's why I would like to address this issue in the set expression if possible.
And if somebody can explain to me why this issue actually occurs or guide me to a source where I can read more about this, it would be great.
Hello!
For sure the empty table does not add any data, but the Hired field is still recognized as a field by Qlik and should work in the set analysis. Do you have any further info regarding this behavior?
Hi Mikel,
Try this
if(len(max(Hired)) > 0, Count({<Hired={$(vThisYear)}>}Hired) ,0)
Hope this help.
Justin.
Hi Justin,
Thank you, this would be a solution!
I would still love to know why the set expression does not work on its own. What might be the reason? Should I now check all my expressions in the app and add If(len(trim(...... to every set expression in order to make sure they are working properly. 😕
Hi Mikel,
I think If Hired is no data, the condition will be remove and then results will be count of all
Hope this help
Justin.
Yes, sounds legit, but this would be against the idea of the set analysis. If I set a condition, I would expect the KPI to return '-' or '0' if the condition is not met.
Could this be a bug in Qlik or everything I know about set analysis is a lie?
Hi Mikel,
check this: Convert Null values using NullAsValue statement in QlikView - YouTube
if you have empty fields, you can fill out with any values, for instance: 'No Values' so you can display within KPI no value.
i hope that helps
Beck