6 Replies Latest reply: Nov 11, 2015 9:58 AM by Gysbert Wassenaar RSS

    Group rows keeping a reference to the row position

    Pieter Boots

      Here is some code i want to share.

      It is a group by statement keeping track of the reference to the detail row.

      It has proven to be usefull a few times.

       

      load * inline [position_row,Groupvalue,sales
      1,     A,     1500
      2,     A,     1200
      3,     A,     956
      4,     A,     120
      5,     B,     2300
      6,     B,     234
      7,     B,     1278
      8,     B,     3456
      9,     C,     1234
      10,     C,     599
      11,     C,     600
      12,     C,     120
      13,     C,     4600
      14,     D,     3511
      15,     D,     3400
      16,     D,     1230
      17,     D,     1200]
      ;


      Maxsales:
      load
       
      Groupvalue,
       
      SubField(maxtext,'|',3) as [grp sales],
       
      SubField(maxtext,'|',2) as [grp position_row];
      LOAD Groupvalue,
          
      Maxstring(text(100000000+sales)  &'|' & text(position_row)&'|' & text(sales)) as maxtext        
      resident testdata
      group by Groupvalue;


      //------Result-----
      //Groupvalue grp position_row grp sales
      //A      1      1500
      //B      8      3456
      //C      13      4600
      //D      14      3511