4 Replies Latest reply on May 25, 2020 12:26 AM by Stefan Spiesmacher

    Pivoting rows to columns fails without error message

    Stefan Spiesmacher



      I have an issue with Tableau Prep Builder 2020.2.1 for which I couldn't find an existing post here.


      The issue is Prep failing to pivot my table from tall to wide. When I drop the field to pivot on in the upper field of the step's operation, Prep starts "Loading all distinct values..." (in the actual data there are a modest 11 of them) for several minutes and then just ends the operation with no result and without even showing the field I dropped. This behavior is independent of first dropping another field in the aggregation part of the step or not.


      To give you an impression, the data looks like this:


      IDTime (Abc)
      Parameter (Abc)
      Value (#)


      The expected result should be like:


      Time (Abc)
      Aa (#)
      Bb (#)


      I deliberately limited my data source during the building process to five IDs and three time steps so I'm pretty sure I don't run into memory issues but have a flow that should run before applying it to the entirity of my data.


      The same behavior can be observed if I, as a test, try to pivot for the time field (with only three distinct values) as a test.


      Has anybody seen this before and, which I hope most, a workaround?


      Best regards



        • 1. Re: Pivoting rows to columns fails without error message
          Mahfooj Khan

          Hi Stefan,


          It's difficult to say what's going wrong, I hope you have tried something similar to below workflow. Is there any chance to see the error screenshot? what's the volume of the data? Have you tried with older versions or tried to pivot some other data sources?



          • 2. Re: Pivoting rows to columns fails without error message
            Stefan Spiesmacher

            Hi Mahfooj,


            thanks for your speedy reply!


            Concerning your questions:

            • Unfortunately, I cannot share a screenshot of the original problem due to the nature of the data. It looks very similar to your screenshot only without "Aa" and "Bb" listed under Parameter: when I drop "Parameter" there from the list of fields, the "Loading all distinct values..."-Screen appears for some minutes, then closes without an error message (and neither a Prep alert) and nothing has happened.
            • The original data is some 200000 IDs which I limited to five for the building process through the Sample function of the data source step.
            • Unfortunately, I don't have older versions available right now. I've done similar pivots (or rather un-pivots) in the past but use them only sparingly due to the long processing times involved so my actual experience is limited.


            In the end, if nobody finds an error I may have made myself, I'm curious whether someone else in the community has had similar issues and hopefully a workaroung or whether this is worthy a bug report.


            Right now I'm avoiding the Pivot step by manually generating fields for each of the eleven Parameters, filling the value with an IIF-function from the Value field and aggregating the result back to the Level of ID and Time. That works reasonably well unless someone adds a new Parameter to the original data...


            Thank you for your support and have a nice weekend!


            Best regards



            • 3. Re: Pivoting rows to columns fails without error message
              Mahfooj Khan

              Hi Stefan,


              Thanks for explaining your use case. Quick question, could you please share the reason for pivoting the source? I have read, tableau efficiently works with depth of the data source and performance degrade with width of the data source.


              And to investigate the prep issue, I'd suggest contact tableau support team by registering a case. They can help you to troubleshoot this issue.

              Support Case | Tableau Software



              • 4. Re: Pivoting rows to columns fails without error message
                Stefan Spiesmacher

                Hi Mahfooj,


                the reason for the pivoting is shaping the table: the original data is very wide (over 400 columns) with a measure appearing in several columns for different dimensions (e.g. the time-steps in my schematic example) and one row per ID. Unfortunately not in a regular fashion (not every measure exists in every time-step), so pivoting down in single steps is very hard to do.




                So I pivoted down all columns (basically a keyvalue format, if I'm not mistaken) and split off the dimensions from the measures in the resulting very tall table. Now I finally want to pivot it up again to have the measures in columns where Tableau Desktop expects them to be.


                Thanks again for your help! I will open a case with the Tableau support team and report back here the results for the community.


                Best regards