2 Replies Latest reply: Nov 4, 2014 5:57 AM by Jacob Baruch RSS

    Script rows values to columns

      Hi,

      I Have two fields that I want to make from them multiple columns, how can I do that?

      example below:

       

      Data: (Date+product+type are the key)

      Date          Product     Type     Price     Percent

      01/01/12     A              1          10          33

      01/01/12     A              1          20          40

      01/01/12     A              1          30          27

      01/01/12     A              2          10          60

      01/01/12     A              2          20          40

       

      Output

      Date          Product     Type     Price1     Percent1     Price2     Percent2     Price3     Percent3

      01/01/12     A              1          10          33               20          40               30          27

      01/01/12     A              2          10          60               20          40         

       

      Thanks!

        • Re: Script rows values to columns
          Dave Riley

          I would create columns using dynamic script like this ...

           

          Data:

          Load *, rowno() as RowRef inline [
          Date,        Product,       Type,      Price,      Percent
          01/01/12,    A,             1,         10,         33
          01/01/12,    A,             1,         20,         40
          01/01/12,    A,             1,         30,         27
          01/01/12,    A,             2,         10,         60
          01/01/12,    A,             2,         20,         40
          ]
          ;

          // identify number of cols to create
          MaxCols:
          Load max(MaxCols) as MaxCols;
          Load count(1) as MaxCols resident Data group by Date, Product, Type;

          //Dynamic script build
          Let s1 = '';
          Let s2 = '';

          for i = 1 to peek('MaxCols')
          let s1 = s1 & 'subfield(Concat(Price,' & chr(39) & ';' & chr(39) & ',RowRef),' & chr(39) & ';' & chr(39) & ',$(i)) as Price' & i & ',';
          let s2 = s2 & 'subfield(Concat(Percent,' & chr(39) & ';' & chr(39) & ',RowRef),' & chr(39) & ';' & chr(39) & ',$(i)) as Percent' & i & ',';
          Next i;

          let s1 = s1 & '0 as a'; //dummy fields so we can handle commas
          let s2 = s2 & '0 as b';

          //Reload data
          Output:
          Load
          Date,
          Product,
          Type,
          $(s1),
          $(s2)
          resident Data group by Date, Product, Type;

          //drop temp tables and fields
          Drop Table Data;
          Drop fields a,b;

           

           

          flipside