topic How to hide List Box values when no matching rows exist? in QlikView

How to hide List Box values when no matching rows exist?

Thu, 03 May 2012 21:51:06 GMT

Hello,

Noob here. I've completed the tutorial and am now digging in. I've imported some data from our data warehouse: some of a fact table of sales, some of the customer records, and the entire Date dimension. All's working well so far, but since my Date dimension includes dates wherein our company didn't even exist yet, my "Year" list box contains years that can't possibly be associated with sales data. How can I hide years in the "Year" list box when no data for those rows exist?

Please pardon, but I don't know what "Categories" to check at the bottom of this discussion thread entry. This is my first thread.

Thanks,
Eric

Re: How to hide List Box values when no matching rows exist?

rwunderlich — Thu, 03 May 2012 23:54:24 GMT

We typically don't try to hide data. It's not the "Qlikview way" (see http://qlikviewnotes.blogspot.com/2012/01/power-of-gray.html). For your case, we limit the dimension data load to match the fact data. Several ways to do this.

1. An explicit range limit on the Date LOAD. For example:

WHERE Year >= year(today(1)) - 4 // last 4 years

2. Load only dates that have facts. Assume your fact date field is OrderDate. On the Date LOAD:

WHERE EXISTS(OrderDate, DateDimField)

3. From the earliest fact date forward:

tempdate:

LOAD min(OrderDate) as mindate RESIDENT Orders;

LET vMindate = peek('mindate');

DROP TABLE tempdate;

On the Date LOAD:

WHERE DateDimField >= $(vMindate)

-Rob

http://robwunderlich.com

Re: How to hide List Box values when no matching rows exist?

Fri, 04 May 2012 02:50:20 GMT

Thanks, Rob. While stewing in my own frustration, it did dawn on me that I should have used some restrictions on the date dimension SQL view that I was using to import date fields. So, instead of:

SELECT * FROM v_dimDate

SELECT * FROM v_factOrders

I should instead be using:

SELECT * FROM v_factOrders

INNER JOIN v_dimDate ON v_factOrders.DateKey = v_dimDate.DateKey

The "good news" is that while I was waiting for a response to my post, I tried just about every single configuration that seemed even remotely related to hiding the year list box data. Luckily, I had the sense not to simply brute force in a select list of years manually simply because they made sense to me. Your "power of gray" article you linked is a great example of why that would have been the wrong approach.

Thanks for all that confirmation. Simply wondering whether or not one's doing it correctly can be sleep-depriving, as I'm sure you know.

On the bright side (wait...wasn't that the bright side?), I'm really enjoying QlikView. The simple beauty and clarity of the app is going to translate to "gee, Eric sure is valuable around here!"

Thanks again,

Eric

How to hide List Box values when no matching rows exist?

rwunderlich — Fri, 04 May 2012 03:30:18 GMT

Welcome to Qlikview. Sounds like you're off to a great start!

-Rob

How to hide List Box values when no matching rows exist?

Sun, 06 May 2012 19:59:41 GMT

Hello Eric.

I am also trying to hide, or more precisely, not havingQlikview show non-selected data in a List Box. It is very annoying and visually interferes having this gray data in the List Box. I don’t think this is the “Qlikview way”, but more of a miss in the program. In the old days, when you found an unexpected behaviour in a programfrom a certain software company, you would be congratulated for finding a “hidden feature”. Maybe this is an Qlikview “hidden feature”.

I read Robs blog on the subject and what he is describing isusing the List Box to debug the data file. That is fine when you are making the dashboard, but, again, confusing for the end user of the dashboard.

So back to the original question, does anybody know how to get rid of this gray data?

Kindly

Marteinn

How to hide List Box values when no matching rows exist?

Sun, 06 May 2012 20:29:54 GMT

Found it!

=aggr(only({<SN=P(SN)>}SN),SN)

It was in another discussion group on the same subject. In my case SN is “Service Name”. I have departments with different services and I needed the list box to show the service names of the department chosen in another list box.

Kindly

Marteinn

Re: How to hide List Box values when no matching rows exist?

YoussefBelloum — Thu, 14 Sep 2017 14:38:07 GMT

Thank you! very useful