1 Reply Latest reply on Feb 24, 2013 10:01 PM by Dean Hewitt

    show 0 where text field does not occur in a subset of data

    Dominic O'Halloran

      Hi there

       

      My problem is that I need to show product counts by product and state in separate tables by state (these variables are both strings). However, some products do not occur in all states. In this instance the product does not appear at all in the table and I would like to show a 0 where it does not exist.

      Has anybody solved this problem, it is driving me nuts!

       

      This is similar to this question here: http://community.tableau.com/thread/121580 but I there is no option to show missing values for strings.

      Also this question: http://community.tableau.com/thread/116339 however modifying the source table and inserting a null record is not an option as it is read only.

       

      Any suggestions are welcome

        • 1. Re: show 0 where text field does not occur in a subset of data
          Dean Hewitt

          Hello Dominic,

           

          You can generate the necessary records as follows;

          1. Create a Select stmt that selects all the products for all the States.  This Select statement will be a Cartesian product.  i.e. there will be a single record for each Product - State combo.   The record can have the necessary product or state info. This is done in the database. 

          2. On the Data Connection screen select multiple tables. Select the Product - State table as the first table. Then join it to the data in question using a left join on Product and state.  This will create the missing records.

           

          Regards

          Dean