Nested Aggr Not Allowed in Script?

[london1980]

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

[Clever_Anjos · Accepted solution]

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