2 Replies Latest reply: Dec 18, 2015 9:06 AM by Alexandru Tatar RSS

    Transpose issue

    Alexandru Tatar

      Hi

       

      My table looks like this:

       

      Header 1Header 2Header 3
      text12012100
      text12013200
      text22014300
      text22015400

       

      I need to make my table look like this:

       

      Header 12012201320142015
      text1100200--
      text2--300400

       

      Right now my script looks like:

       

      If ([Header 2]=2012,[Header 3]) as 2012,

      .

      .

      .

      and so on.

       

      And the result looks like:

       

      Header 12012201320142015
      text1100---
      text1-200--
      text2--300-
      text2---400

       

      Any ideas on why this happens?

       

      Thank you!

        • Re: Transpose issue
          Gysbert Wassenaar

          You have four source records and you end up with four records because you're not aggregating anything. I don't know why you think you need to transpose the data. In the front end you can simply use a pivot table with Header1 and Header2 as dimensions and sum(Header3) as expression to get the result you want. But if you feel that you need to do this in the script then you need to add an extra load statement to create the result table:

           

          Result:

          NOCONCATENATE LOAD

               Header1,

               Sum(2012) as 2012,

               Sum(2013) as 2013,

               Sum(2014) as 2014,

               Sum(2015) as 2015

          Resident YourFirstTable

          Group By Header1;

           

          Drop Table YourFirstTable;

           

          Replace YourFirstTable with the name of the table you now create with your if- statements.

            • Re: Transpose issue
              Alexandru Tatar

              Thank you Gysbert for the prompt answer, much appreciated! I couldn`t use a pivot table because the database has more than 10 million rows x 25 columns and calculations take a lot of time. This is why I wanted to do most of the job done at the script level and display the results in a basic table. Meanwhile we temporarily solved the issue by taking only 2015 into account.

               

              Have a great day!