6 Replies Latest reply: Apr 7, 2015 5:17 AM by Telmo Duarte RSS

    Data aggregation / Group By question

    Telmo Duarte

      Hi,

      I was trying to do a Group By similar to SQL when the load contains an if condition.

      It was giving me an "Invalid Expression" error so I added all fields not aggregated in the Group By.

       

      DataTemp:

      LOAD * INLINE [

          Vendor, fail, Units

          A, 1, 1

          B, 1, 2

          C, 0, 3

          D, 0, 4

          D, 0, 10

          E, 0, 5

      ];

       

      Data:

      LOAD

        if(fail=1,'Other',Vendor) as Vendor,

        Sum(Units) as Units

      Resident DataTemp

      Group by Vendor, fail;

       

      Drop Table DataTemp;

      =========================================

      Result:

      VendorUnits
      Other1
      Other2
      E5
      D14
      C3

       

      The code works but it's not aggregating all rows as I intended.

      What I want is this:

      VendorUnits
      Other3
      E5
      D14
      C3

       

      Is there a way of achieving this without doing another Resident Load with another Group By?