4 Replies Latest reply: May 12, 2016 3:00 AM by Alexander Steinhart RSS

    transform table

    Alexander Steinhart

      Hello,


      I have a table which needs to transform.

      My table A looks like this:


      A:

      LOAD

      Key,

      quantity,

      Price

      FROM....;

       

      KeyquantityPrice

      1

      11.50
      153.70
      1104.80

      2

      12.45
      258.45
      7101.28
      316.45
      345.45
      618.45
      686.21
      36

      8.45


      From this base, I need I table like this:


      Keyquantity1Price1quantity2Price2quantity3Price3
      111.5053.70104.80
      212.4558.45
      316.4545.4568.45
      618.4586.21
      7101.28


      Have someone a solution for this problem?


      I have an idea, but I don't know how to implement.


      I sort my table. First the quantity.

       

      KeyquantityPrice
      111,50
      212,45
      316,45
      618,45
      345,45
      153,70
      258,45
      368,45
      686,21
      1104,80
      7101,25


      In the second the Key

       

      KeyquantityPrice
      111,50
      153,70
      1104,80
      212,45
      258,45
      316,45
      345,45
      368,45
      618,45
      686,21
      7101,25



      Now I need a new colum.

       

      KeyquantityPricenumberOfKey=
      111,50

      =if(

      key(from row) = key(from row -1),

      then

      numberOfKey(from row-1) + 1,

      else

      1

      )

           1
      153,70...2
      1104,803
      212,451
      258,452
      316,451
      345,452
      368,453
      618,451
      686,212
      7101,251


      If I get a table like this I can transform these.


      newA:

      LOAD

      Key,

      quantity1,

      Price1

       

      FROM A

      where numberOfKey = 1;

       

      outer join (newA)

      LOAD

      Key,

      quantity2,

      Price2

      FROM A

      where numberOfKey = 2;

      ....

       




      I hope somebody can help me.


      Greetings

      Alex