2 Replies Latest reply on May 21, 2015 3:49 PM by Mark Labovitz

    Determine if a value occurs in one of multiple dimensions

    Mark Labovitz

      I have received a dataset each of whose records is an order.  A maximum of five products (out of 4,000+ products) may be purchased on each order.   There are five dimensions lets call them product_1, product_2, product_3, product_4 and product_5.  On a given record product_1 will always have a non_NULL value, one of the 4,000+ and the other 4 dimensions may be NULL on the record; on another record product_1 and product_2 may have a non-NULL values (any two without replacement of the 4,000+) and rest of the product dimensions NULL. There are records with 3, 4, and 5 non-NULLs with any value from each of the 4,000+ available. There is no restriction on which product will appear in which dimension only that if product_d is non-NULL, product_1 to product_d-1 will also be non-NULL.  My question is how do I (can I) filter within Tableau for any given product or multiple products so that I pull all the records which has the product(s) of interest in any of the five product dimensions of the record.  Thanks for any assistance.

       

      Regards,

       

      Mark Labovitz

        • 1. Re: Determine if a value occurs in one of multiple dimensions
          Shine Pulikathara

          Hi,

           

          If you have flexibility in restructuring the data table, I would suggest converting the five product columns into 2 columns: say Product Number (with values from 1 to 5) and Product ID or Name (the 4000+ names or IDs of the products). Then you can set up a Quick Filter on the Product ID/Name field to get the appropriate orders. If your data source is Excel or text-based you could use the Pivot functionality in Tableau 9 to do this.

           

          An alternative approach might be to use a parameter to search across the 5 fields. For instance, see the attached workbook. I have used the "Sample Superstore" data set to illustrate. I created a Search Name Parameter and created a calculated field called "Found" with the following formula, so the user can enter a search string that finds a match in either Ship Mode, Category or Sub-Category fields. You could use the CONTAINS function for partial matching. I then added this field to the filter pane, and set it to show only the records with value 1.

           

          IF ([Ship Mode] = [Search Name]) OR
             ([Category]=[Search Name]) OR
             ([Sub-Category]=[Search Name])
          THEN 1
          ELSE 0
          END
          
          

           

          In your case, you would use [product_1] = [Search Name], [product_2]=[Search Name], etc. in the IF condition.

           

          Hope this helps.

          • 2. Re: Determine if a value occurs in one of multiple dimensions
            Mark Labovitz

            Hi Shine --

             

            Many thanks for your response.  Got trapped in other items this week so have not had a chance to review your suggestions.  Will do so shortly and get back on the forum with my observations.

             

            Regards,

             

            Mark Labovitz