11 Replies Latest reply on Aug 30, 2016 2:46 PM by Brian Crawford

    Multiple Pivots

    osian.jones.1

      Hi,

       

      I am importing data where there are for each user ID:

       

      50 columns containing product categories (0-1 propensity score on buying each product)

      20 columns containing sales channels (0-1 propensity score for using each channel)

       

      I've Pivoted the data on the first set of 50 columns and created a "Product" field name, but if I want to create a column for the additional set (20) it seems to only be able to add this to the first set rather than create a new pivot field.

       

      Is there a way around this other than organising the data before importing to Tableau?

       

      Thanks

        • 1. Re: Multiple Pivots
          Thomas McCullough

          Osian,

           

          The Tableau data connector will only let you pivot once, so all the data for the pivot needs to be there first.  It should work fine though, but pivoting 70 columns instead of 50 then 20.

           

          Cheers,

           

          Tom

          • 2. Re: Multiple Pivots
            osian.jones.1

            Thanks, but the 50 and 20 mean different things, one is product and other is channel. So by collapsing into a single column it would effectively combine these two variables which I wouldn't want.

             

            I guess I should either preprocess the data before importing Tableau (i.e. with just 2 cols for product and channel data)?

             

            Thanks

            • 3. Re: Multiple Pivots
              Thomas McCullough

              Preprocessing will solve most things for Tableau.

              • 4. Re: Multiple Pivots
                Chris McClellan

                Agreed, there's no way to do this currently in Tableau.  I usually use Alteryx to do stuff like this before accessing the data in Tableau.

                • 5. Re: Multiple Pivots
                  Tanner Weil

                  I had a similar issue. I resolved it by creating a copy of my data source. Then, added a new data source (the copy), and pivioted the second set of values.

                  Hope that helps!

                  1 of 1 people found this helpful
                  • 6. Re: Multiple Pivots
                    Brian Crawford

                    I would use a similar approach, but for each data connection that used pivots, I would drop all the remaining fields except for the primary key and the fields involved in the pivots.  This allows you to blend (or join) the pivot values to the appropriate row without the excess baggage of the redundant fields.

                    1 of 1 people found this helpful
                    • 7. Re: Multiple Pivots
                      ahmed.hasnaoui

                      and if data source needs to be refreshed would the copied data source also refresh? Or do you have to do the process again?

                      • 8. Re: Multiple Pivots
                        Brian Crawford

                        The method I described utilizes a separate data source for each pivot, therefore even though these multiple data sources pull from the same source, they must be refreshed individually.  Other than that, I find that they work well and as you would expect them to perform.

                        1 of 1 people found this helpful
                        • 9. Re: Multiple Pivots
                          kevin knorpp

                          I know this is ugly and also possibly not recommended, but only as a lesser evil (depending on circumstances) it seems like it would be possible to put all seventy fields in the same pivot but then separate the data in calculations? 

                           

                          Ugly, I know, but seems to be an alternative.  I haven't tried it yet but I have the same need so will report back with results of my test. 

                          • 10. Re: Multiple Pivots
                            Berenice Vega

                            Hi Tanner,

                             

                            I have a question, how did you pivoted the second set of values.  I added the new data source but since it already joins it to the database it is not allowing me to do a new pivot and I don´t know how to add the new data without making a join.

                             

                            Regards,

                            • 11. Re: Multiple Pivots
                              Brian Crawford

                              I found two ways to handle this that work for me.

                              1) if you are OK with a blend, you can just add the table again with the "+" to create a new data source, blend them on the shared primary key, and use different pivot fields in each data source.

                              2) can make a copy of the excel sheet in a second tab and then drag in the copied tab into the data area and join them on the common primary key; again using different pivot choices in each. 

                               

                              On on either approach, to reduce clutter and memory resources you can hide the fields not used in each tab.

                              1 of 1 people found this helpful