2 Replies Latest reply: Jul 27, 2016 2:54 PM by Christian Wolf RSS

    Partial Transpose

    Karunpreet Soni

      Hello All,

       

      I have a table

         

      C1C2C3T1T2T3
      1a9999
      1b5555
      1c8888
      2d2220
      2e4440
      3f3300
      4g7777
      4h0000
      4i6666
      5j7700

       

      I want to transpose T1,T2 and T3 only without changing there field names and doing it for respective C1.

       

      Expected Output

       

         

      C1C2C3T1T2T3
      1a9958
      1b5958
      1c8958
      2d2240
      2e4240
      3f3300
      4g7706
      4h0706
      4i6706
      5j7700

       

      Thanking you all in anticipation

       

      Regards

      KP  

        • Re: Partial Transpose
          rodrigo silva

          maybe this:

           

          table:

          LOAD *, RowNo() AS LINHA;

          LOAD * Inline [

          C1, C2, C3, T1, T2, T3

          1, a, 9, 9, 9, 9

          1, b, 5, 5, 5, 5

          1, c, 8, 8, 8, 8

          2, d, 2, 2, 2, 0

          2, e, 4, 4, 4, 0

          3, f, 3, 3, 0, 0

          4, g, 7, 7, 7, 7

          4, h, 0, 0, 0, 0

          4, i, 6, 6, 6, 6

          5, j, 7, 7, 0, 0

          ];

           

           

           

           

          teste:

          LOAD

          C1,

          FirstSortedValue(T1,LINHA) AS TESTE1 Resident table Group BY C1;

           

           

          Left Join(teste)

          LOAD

          C1,

          FirstSortedValue(T2,LINHA,2) AS TESTE2 Resident table Group BY C1;

           

           

          Left Join(teste)

          LOAD

          C1,

          FirstSortedValue(T3,LINHA,3) AS TESTE3 Resident table Group BY C1;

           

           

          Left Join(teste)

          LOAD

          C1,

          FirstSortedValue(T2,LINHA) AS TESTE21 Resident table Group BY C1;

           

           

          Left Join(teste)

          LOAD

          C1,

          FirstSortedValue(T3,LINHA) AS TESTE31 Resident table Group BY C1;

           

           

           

           

           

           

          Table1:

          LOAD C1,TESTE1 as t1, TESTE2 as t2, TESTE3 as  t3 Resident teste Where not IsNull(TESTE2) and not IsNull(TESTE3);

           

           

          Concatenate(Table1)

          LOAD C1,TESTE1 as t1, TESTE2 as t2, TESTE31 as  t3 Resident teste Where not IsNull(TESTE2) and  IsNull(TESTE3);

           

           

          Concatenate(Table1)

          LOAD C1,TESTE1 as t1, TESTE21 as t2, TESTE31 as  t3 Resident teste Where  IsNull(TESTE2) and  IsNull(TESTE3);

           

           

          Inner Join(Table1)

          LOAD C1,C2,C3 Resident table; DROP Table table;DROP Table teste;