3 Replies Latest reply on Oct 1, 2013 12:19 AM by Jim Wahl

    Aggregating strange dataset

    CJ Ragnarsson

      I have a dataset which is a bit strange. For each customer, it will list his 3 preferred product groups (in order of spending) and how much money he spent in those groups. E.g.

       

      Albert, Books, 10, PC, 8, Home, 7

      Bob, PC, 20, Electronics, 8, Books, 6

       

      Without reshaping my data-set, is it possible to get an aggregate like this:

       

      PC 28

      Books 16

      Electronics 8

      Home 7

       

      which is the sum of the values in the 3 corresponding columns?

       

      Is it possible to get:

       

      PC 20, 8, 0

      Books 10, 0, 6

      Electronics 0, 8, 0

      Home 0, 0, 7

       

      which in columns has how much each product group contributed to the first 3 positions?

       

      I realize that this would be much easier if the Top 3 groups happened to be in rows instead, but right now I don't have this luxury.

        • 1. Re: Aggregating strange dataset
          Matt Lutton

          Can you post an example data set in a packaged workbook (.TWBX) file?  That will help us help you.

          • 2. Re: Aggregating strange dataset
            Jim Wahl

            What database are you using? You're probably better off doing this with custom SQL, since you need to both split the rows and unpivot them. I.e., you want the final data to look something like:

             

            NameCategoryRankAmount
            AlbertBooks110
            AlbertPC28
            AlbertHome37
            BobPC120
            BobElectronics28
            BobBooks36

             

            I'm sure it's possible to do this in Microsoft Jet (the database connection used for text, Excel and Access data sources), but it might be ugly and slow with a bunch of string manipulation. On the other hand it's a straight forward Python script (or any other script tool, including VBA (?)). And if you're using a proper database, you might have a more efficient split function.

             

            Jim

            • 3. Re: Re: Aggregating strange dataset
              Jim Wahl

              Another quick thought. If your your example data above is your data file, then you could add a header row, connect as text and use a simple custom SQL connection to reshape the data.

               

              Data Source Text File:

              Albert, Books, 10, PC, 8, Home, 7
              Bob, PC, 20, Electronics, 8, Books, 6
              


              Add Header Row:

              Customer, Cat1, Amt1, Cat2, Amt2, Cat3, Amt3
              Albert, Books, 10, PC, 8, Home, 7
              Bob, PC, 20, Electronics, 8, Books, 6
              

               

              You can append a header row in Windows with:

              echo Customer, Cat1, Amt1, Cat2, Amt2, Cat3, Amt3 > data.txt
              type OriginalDataFile.txt >> data.txt

               

              If you're using linux, just replace type with cat.

               

              Connect to text data source in Tableau and select Custom SQL: 

              SELECT 
                [data#txt].[Customer] AS [Customer],
                "1" as [Rank],
                [data#txt].[Cat1] AS [Category],
                [data#txt].[Amt1] AS [Amount]
              FROM [data#txt]
              UNION ALL
              SELECT 
                [data#txt].[Customer] AS [Customer],
                "2" as [Rank],
                [data#txt].[Cat2] AS [Category],
                [data#txt].[Amt2] AS [Amount]
              FROM [data#txt]
              UNION ALL
              SELECT 
                [data#txt].[Customer] AS [Customer],
                "3" as [Rank],
                [data#txt].[Cat3] AS [Category],
                [data#txt].[Amt3] AS [Amount]
              FROM [data#txt]
              

               

              The data now looks like

              CategoryCustomerRankAmount
              BooksAlbert110
              PCBob120
              PCAlbert28
              ElectronicsBob28
              HomeAlbert37
              BooksBob36

               

              And you can get your result easily in Tableau:

              2013-10-01 10-08-53.png

               

              Jim