2 Replies Latest reply on Mar 8, 2013 1:09 PM by Benjamin Ash

    Pivot Table Like Filtering in Tableau - Eliminating the * Record

    Benjamin Ash

      I am blending excel data with data from a SQL database.  The data is something like a list of products in the excel data set.  The SQL data set contains customer transactions in those products and a corresponding customer type.  When I try and setup a filter for the customer type I have a "*" value in the list which indicates instances where multiple different customer types have purchased a product. 


      The outcome I'm looking for is a list of the details without this multiple aggregation.  I've been looking through the online documentation but can't seem to find anything.  Any pointers would be very helpful!



        • 1. Re: Pivot Table Like Filtering in Tableau - Eliminating the * Record
          Catherine Rivier


          Without a sample workbook to go off of, I can only give you an outline....


          The reason you see the * is, as you said, because of multiple aggregation.  Your primary data source has full flexibility and will show you the data aggregated however you choose.  Your secondary data source does not have that flexibility.  It is, essentially, "pre-aggregated" by whatever your linking fields (the relationships) are.


          What Tableau does is issue two queries, one to the primary data source and one to the secondary. The query on the secondary is an aggregate query at the level of detail defined by the linking fields in the view that will only return one record per combination of the values of the linking fields, whereas a normal left join would return as many rows as were in the secondary that met the linking criteria. Once the data is returned inside Tableau, the secondary is "left joined" back to the primary.  Jonathan Drummey explains it really well here: http://community.tableau.com/thread/121954


          So your primary, it sounds like, is aggregating only on Product, which is your linking field.  Your secondary sometimes has multiple records per Product, with different Customer Types.  What you'd like to see is every combination of Product, Customer Type, right?


          In a nutshell, here are the two easiest ways to solve this:

          1) Switch your primary and secondary data sources.  (You want your primary to be the data source with all of the possible combinations you want to see.  It sounds like that's the source you're currently using as the secondary...)

          2) Create a new data source that contains every combination of Product and Customer Type - just a simple list.  Use this as your primary, and have both of your other data sources as secondary.


          Hope this helps, and if you put in a sample workbook, can hopefully help further!


          • 2. Re: Pivot Table Like Filtering in Tableau - Eliminating the * Record
            Benjamin Ash

            Thanks Catherine!  Your description and the link were both very helpful in coming to grips with the problem.  I played with switching the primary source and have a good feel of what happens in each instance. 


            However, since I'm relatively new to Tableau, I was wondering if there might be a third solution along the lines of creating two sheets with the data sources in each orientation and then overlaying the resulting data (I'm working with a chart).  I know this is a bit vague but I guess the ultimate question is:  Is it possible to show two different data orientations on the same sheet?


            For now I am going to work on your 2nd suggestion as I believe it will do what I'm looking for.  Any suggestions for a tool to do that well?  I'm working with a SQL database I have very little control over and a large amount of data in excel.  I started by trying to use Powerpivot but it is reliably unreliable and crashes frequently.