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: 
happydays1967
Creator
Creator

Dynamically filter on Null in charts and tables

Hello,

I have been developping QV applications for a number of years now, but not found a solution to this problem. There are quite a number of posts about the subject, but none answer my (and sometimes others) question. I will try to be as clear as possible to describe the issue and hope someone can come up with a solution. As far as I have seen up to now this is a very important missing feature in QlikView. Because sometimes it is important to be able to not only analyse the data we have, but also the data we DON'T have.

Issue is that because of the way data is combined, we are confronted with a measure that is not always present. Let me give an example: we have companies and orders. It would be very simple to say that we only want to analyse customers i.e. companies WITH orders. But what if we want to make an analyses on ALL companies with the number of orders in a certain timeframe. And I then want to filter out the companies that have NULL orders. This of course is a very simple example, and I have gotten around this using the aggr() function. But say I want to be able to do this for any possible combination of dimensions in any of my charts or tables. The data is combined as outer joins, so it is well possible that there will be a combination of present values plus a number that miss values. I cannot solve it in my script because the value in the table is always populated (for instance OrderID). As far as I know it is not possible to filter on this 'NULL group' in any given analyses. It is possible to exclude the nulls for any dimension, but I simply cannot click on the '-' group to see which companies (in my example) are missing values.

I cannot determine which combination of data will cause the NULL group to happen in which dimension and, as mentioned before, the dimension itself will never be NULL so scripting changes are not really an option I think.(believe me, I have tried a lot of things).

So is there any way that we can somehow dynamically filter on the dimension 'lack-of-value' caused by outer joining the data?

Any comment would be greatly appreciated.

Hans Peter

EDIT: I have created the simple example qvw attached to show what I mean hopefully more clearly.

4 Replies
Anonymous
Not applicable

Hi,

Doesn't 'nullasvalue' work? Give it a try if not.

happydays1967
Creator
Creator
Author

Nope, this will not work as it is a script operator. As stated, in the tables themselves the value will not be null, only in the resulting joins that occur in the front-end charts can result in Null for some combinations.

Besides, we would have to create a NullAsValue for a large amount of fileds, plus this would not be a very maintainable solution when we add fields and tables to our datamodel.

But thanks for thinking along!

HP

hariprasadqv
Creator III
Creator III

Suppress null values option may gives you some solution.

happydays1967
Creator
Creator
Author

I don't see how, as it would achieve the opposite of what I want. I have edited the original post and added a very simple qvw to explain the issue.

Thanks.

HP