Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
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