Nested Aggr Not Allowed in Script?

london1980 — Sat, 26 Mar 2016 12:01:05 GMT

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

Re: Nested Aggr Not Allowed in Script?

Clever_Anjos — Sat, 26 Mar 2016 12:15:24 GMT

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

topic Nested Aggr Not Allowed in Script? in QlikView

Nested Aggr Not Allowed in Script?

Re: Nested Aggr Not Allowed in Script?