Question regarding set analysis

[mikel_de]

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!

[juliocodesal]

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.

[shivanandk]

Add a record with Null values to the column, The expression will work.

Hired: 

LOAD  

* 

INLINE  

[ 

ID,Hired 

Null(),Null()

] 

(DELIMITER IS ','); 

[mikel_de]

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.

[mikel_de]

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?

[justinphamvn]

Hi Mikel,

Try this

if(len(max(Hired)) > 0,  Count({<Hired={$(vThisYear)}>}Hired) ,0)

Hope this help.

Justin.

[mikel_de]

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. 😕

[justinphamvn]

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.

[mikel_de]

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?

[beck_bakytbek]

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