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

    Pivoting rows to columns fails without error message

    Stefan Spiesmacher

      Hi,

       

      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 (#)
      ID1T1Aa1
      ID1T1Bb1.5
      ID1T2Aa1.2
      ID1T2Bb2
      ID2T1Aa0.9
      ............

       

      The expected result should be like:

       

      ID
      Time (Abc)
      Aa (#)
      Bb (#)
      ID1T111.5
      ID1T21.22
      ID2T10.9...
      ID2T2......

       

      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

       

      Stefan

        • 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?

           

          Mahfooj

          • 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

             

            Stefan

            • 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

               

              Mahfooj

              • 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.

                 

                ID
                Aa_T1
                Aa_T2
                Bb_T1
                BbT2
                ID1
                ID2
                ID3

                 

                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

                 

                Stefan