Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mikel_de
Creator
Creator

Question regarding set analysis

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!

1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

I'd say it's a bug. I'll report it.

HIC

View solution in original post

14 Replies
juliocodesal
Contributor III
Contributor III

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
Partner - Creator II
Partner - Creator II

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

Hired: 

LOAD  

INLINE  

ID,Hired 

Null(),Null()

(DELIMITER IS ','); 

mikel_de
Creator
Creator
Author

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
Creator
Creator
Author

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
Creator II
Creator II

Hi Mikel,

Try this

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

Hope this help.

Justin.

mikel_de
Creator
Creator
Author

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
Creator II
Creator II

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
Creator
Creator
Author

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
Master
Master

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