Script load Error in expression: Nested aggregation not allowed

Peter_Brunner — Tue, 22 Aug 2017 03:36:01 GMT

Hi all,

I am having some trouble replicating a (working set analysis formula) to load in script as I wish to export the results to a table

and keep getting Error in expression: Nested aggregation not allowed

the working variable set analysis syntax

SL_ON_HAND_QTY-

if(Sum({$<TRANS_TYPE={''AAA'',''BBB''}>}ENTERED_QTY)>''0'',Sum({$<TRANS_TYPE={''AAA'',''BBB''}>}ENTERED_QTY),''0'')

if(Sum({$<TRANS_TYPE={''CCC''},STOCK_LOCATION={''2D44'',''2D55''}>}ENTERED_QTY)>=''0'',

Sum({$<TRANS_TYPE={''CCC''},STOCK_LOCATION={''2D44'',''2D55''}>}ENTERED_QTY),''0'')

if(Sum({$<TRANS_TYPE={''DDD''}>}ENTERED_QTY)>=''0'',Sum({$<TRANS_TYPE={''DDD''}>}ENTERED_QTY),''0'')>0,

SL_ON_HAND_QTY-

if(Sum({$<TRANS_TYPE={''AAA'',''BBB''}>}ENTERED_QTY)>''0'',Sum({$<TRANS_TYPE={''AAA'',''BBB''}>}ENTERED_QTY),''0'')

if(Sum({$<TRANS_TYPE={''CCC''},STOCK_LOCATION={''2D44'',''2D55''}>}ENTERED_QTY)>=''0'',

Sum({$<TRANS_TYPE={''CCC''},STOCK_LOCATION={''2D44'',''2D55''}>}ENTERED_QTY),''0'')

if(Sum({$<TRANS_TYPE={''DDD''}>}ENTERED_QTY)>=''0'',Sum({$<TRANS_TYPE={''DDD''}>}ENTERED_QTY),''0''),0)

My attempt to replicate above .....load in script below any help on correct way to handle this appreciated

Error in expression:

Nested aggregation not allowed

CaptureTotals:

LOAD

BUYER_CODE,

Sum(SL_ON_HAND_QTY*STANDARD_COST) as TOTAL_OH_QTY,

SUM(SL_ON_HAND_QTY)-

SUM(if(MATCH(TRANS_TYPE,'AAA','BBB',ENTERED_QTY)>='0', Sum(MATCH(TRANS_TYPE,'AAA','BBB', ENTERED_QTY)))),'0'-

SUM(if(MATCH(TRANS_TYPE,'CCC' AND MATCH(STOCK_LOCATION,'2D44','2D55',ENTERED_QTY)>0,sum(MATCH(TRANS_TYPE,'CCC' AND MATCH(STOCK_LOCATION,'2D44','2D55'),ENTERED_QTY))),'0'-

SUM(if(MATCH(TRANS_TYPE,'DDD',ENTERED_QTY)>0,sum(MATCH(TRANS_TYPE,'DDD',ENTERED_QTY)))))),

SUM(SL_ON_HAND_QTY)-

SUM(IF(MATCH(TRANS_TYPE,'AAA','BBB',ENTERED_QTY)>='0', Sum(MATCH(TRANS_TYPE,'AAA','BBB', ENTERED_QTY)))),'0'-

SUM(IF(MATCH(TRANS_TYPE,'CCC' AND MATCH(STOCK_LOCATION,'2D44','2D55',ENTERED_QTY)>0,sum(MATCH(TRANS_TYPE,'CCC' AND MATCH(STOCK_LOCATION,'2D44','2D55'),ENTERED_QTY))),'0')-

SUM(IF(MATCH(TRANS_TYPE,'DDD',ENTERED_QTY)>0,sum(MATCH(TRANS_TYPE,'DDD',ENTERED_QTY))))) AS SlowQtyScript

Resident FLAGS

where STOCK_LOCATION = '2D44'

group by BUYER_CODE, TRANS_TYPE,

thanks Peter

Re: Script load Error in expression: Nested aggregation not allowed

Anil_Babu_Samineni — Tue, 22 Aug 2017 03:38:43 GMT

Mainly debug and highlight that line. Then we can better understand

Re: Script load Error in expression: Nested aggregation not allowed

Peter_Brunner — Tue, 22 Aug 2017 03:50:42 GMT

Hi Anil,

that is exactly what I have done copy and pasted the debug screen above with what it returned

directly below where the "Error in expression:" is.

thanks Peter

Re: Script load Error in expression: Nested aggregation not allowed

Anil_Babu_Samineni — Tue, 22 Aug 2017 03:55:30 GMT

In fact, it is weird contract if we required in script. But, Try a luck use RangeSum() for your 2 Sum() expressions. I don't prefer to use this as script due to nested aggregation issue. Can this possible to use same in UI rather Script.

topic Re: Script load Error in expression: Nested aggregation not allowed in QlikView

Script load Error in expression: Nested aggregation not allowed

Re: Script load Error in expression: Nested aggregation not allowed

Re: Script load Error in expression: Nested aggregation not allowed

Re: Script load Error in expression: Nested aggregation not allowed