Dynamically filter on Null in charts and tables

[happydays1967]

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.

[Anonymous]

Hi,

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

[happydays1967]

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]

Suppress null values option may gives you some solution.

[happydays1967]

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