Script load Error in expression: Nested aggregation not allowed

[Peter_Brunner]

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

[Anil_Babu_Samineni]

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

[Peter_Brunner]

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

[Anil_Babu_Samineni]

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.