3 Replies Latest reply: Nov 23, 2014 6:33 PM by Marco Wedel RSS

    Problem Transforming Data

    Bill Markham

      Hi

       

      I have this format of input data. Which has a Header rows and Detail rows.  It comes in this order and also has a Row number to sort the data if needed.

       

      Row Description Type Value
      1HAHeader
      2aDetail1
      3bDetail2
      4cDetail3
      5dDetail4
      6HBHeader
      7eDetail5
      8HCHeader
      9fDetail6
      10gDetail7
      11hDetail8
      12iDetail9

       


      And I would like to transform it into this format, such that the the Header Description is repeated until a new Header.

       

      Row Header Detail Value
      1HAHA
      2HAa1
      3HAb2
      4HAc3
      5HAd4
      6HBHB
      7HBe5
      8HCHC
      9HCf6
      10HCg7
      11HCh8
      12HCi9

       

      I thought it would be easy but have completely failed.

       

      Any suggestions ?

       

      ****************************************************************

       

      Here is an inline load to create the input data

       

      InputData :

      LOAD * INLINE [

          Row, Description, Type, Value

          1, HA, Header

          2, a, Detail, 1

          3, b, Detail, 2

          4, c, Detail, 3

          5, d, Detail, 4

          6, HB, Header

          7, e, Detail, 5

          8, HC, Header

          9, f, Detail, 6

          10, g, Detail, 7

          11, h, Detail, 8

          12, i, Detail, 9

      ];