3 Replies Latest reply: Oct 18, 2011 9:57 AM by Jonathan Dienst RSS

    Data sub selection in Table/Straight table/Pivot Table



      I am trying to build a couple of simple tables and I am looking for help/confirmation. Lets say you have

      a set of data with 100 rows, and 10 columns. In column 2 there will be some blank values but that is

      fine as you want to identify those columns.


      I create a table box and add all the columns as I want to see those values without any calculations

      performed against them. However, I do want to only see a subset of the data as I only want to see data

      where column 2 has no value, null.


      Question 1: What is the best way to achieve this? ( Essentially the equivalent of the where clause in

      SQL statements )


      Question 2: Is the same method applicable to Pivot and Straight tables?


      Many Thanks



        • Re: Data sub selection in Table/Straight table/Pivot Table
          Jonathan Dienst



          you cannot normally select NULL values in tables or charts.


          What I normally do in this sort of case is set the null values to something like "MISSING", or "UNKNOWN" in the load script. Then they can be selected like any other value.


          Use something like




               If(Len(field) = 0, 'UNKNOWN', field) As field

               ... or ...

               If(IsNull(field), 'UNKNOWN', field) As field



          Hope that helps


            • Re: Data sub selection in Table/Straight table/Pivot Table

              Hi Jonathan,


              thank you for your response, this is exactly the approach I have take for the moment and have modified my load script accordingly.


              so lets say that you do have the value 'UNKNOWN' in your dataset where the null value is in column 2. Now when i put that into my table box/straight table/pivot table how can that be defaulted to only that specific set of data without the user having to click anything or indeed being able to undo that initial selection criteria, i.e. all data where column 2 = 'UNKNOWN'? Is it something like a condition statement on the object, or does every dimension included have to be calculated and include a restrictive statement based on the 'UNKNOWN' value. In effect have a pre-filtered dataset in the object.



              Many Thanks