5 Replies Latest reply: May 6, 2012 4:29 PM by Marteinn Magnusson RSS

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

ericbragas

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?
    Rob Wunderlich

    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?
      ericbragas

      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?
    Marteinn Magnusson

    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?
    Marteinn Magnusson

    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