1 Reply Latest reply: Mar 26, 2016 8:01 AM by Mahm Tamal RSS

    Nested Aggr Not Allowed in Script?

    Mahm Tamal

      Hi All,

       

      I'm trying to create a new table based on my Resident table.

       

      Here is my script:

       

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

      LOAD

           Customer,

           SUM(Invoice_Value) as Monthly_Invoice_Value,

           SUM (IF(Week_Commencing=MAX(Week_Commencing), Invoice_Value,0)) as Week_04

       

      Resident Invoice_Table

       

      Group By Customer

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

      At the moment it's giving an error as "Nested aggregation not allowed"

       

      If I replace max(Week_Commencing) with a fixed value as '21/03/2016' then it works, but Week_Commencing is dynamic so I need to use max(Week_Commencing). Is there any way around this No Nested Aggr rule?

       

      thanks

      MT

        • Re: Nested Aggr Not Allowed in Script?
          Clever Anjos

          You should perform a 2-step load

           

          left join(Invoice_Table)

          LOAD

               Customer,

               MAX(Week_Commencing) as MAX_Week_Commencing

          Resident Invoice_Table

          Group By Customer


          LOAD

               Customer,

               SUM(Invoice_Value) as Monthly_Invoice_Value,

               SUM (IF(Week_Commencing=MAX_Week_Commencing, Invoice_Value,0)) as Week_04

          Resident Invoice_Table

          Group By Customer