4 Replies Latest reply: Aug 4, 2011 9:00 AM by Edward Hart RSS

    New sum in load script dilema

      Hi, im still having a headach with using 'sum' in the load script.  I am trying to generare the table below: -

       

      Refnoshouldbe
      1114
      1414
      1514
      214
      214
      214
      1114
      1114
      214
      313
      313
      313
      1114
      1114

       

      I am trying to do the sum of 'no' grouped by Ref to produce and additional column that matches 'shouldbe'.

       

      The code I am using is: -

       

      Main:
      LOAD Ref,
           [no],
           shouldbe
      FROM
      data.xlsx
      (ooxml, embedded labels, table is Sheet1);


      Main2:
      LOAD Ref as ref2,
        [no],
        sum([no]) as sum,
           shouldbe


      RESIDENT Main
      GROUP BY Ref , [no], shouldbe ;
      DROP TABLE Main;

       

       

      However the table that is being produced looks like this :-

       

      ref2nosumshouldbe
      3133
      14414
      2144
      11514
      15514

       

      I only want the load to produce one 'ref 1' with the sum being 14.

       

       

      I'm baffled, any help would be greatly received.

       

      Many thanks