Invalid Expression

[#1]

Dear Community,

I have the below script - the Sum formula, although complicated, works perfectly well as an expression in a straight table but when I try to put it in the script I get an "Invalid expression" error - can anyone help?

Left Join (Quotes)
Load
quote,
Supplier,
sum(
if(type='Parcel',
if(items=[Parcel Up to],
([Base Cost])+([Additional KG]*(if(payweight2>[Weight Finish],payweight2-[Weight Finish],0))),
(([Base Cost])+([Additional KG]*(if(ceil(payweight2/items,1)>[Weight Finish],ceil(payweight2/items,1)-[Weight Finish],0)))*[Parcel Up to])
+
(([Additional Parcel Base])+([Additonal Parcel KG]*(if(ceil(payweight2/items,1)>[Weight Finish],ceil(payweight2/items,1)-[Weight Finish],0))))*(items-[Parcel Up to])),
([Base Cost])+([Additional KG]*(if(payweight2>[Weight Finish],payweight2-[Weight Finish],0))))) as CalculatedCost
Resident Quotes;

[ankit777 · Accepted solution]

Hi

Are you missing group by in resident load?

[PrashantSangle]

Sum() is aggregated function.

You need group by clause.

Regards

[Kushal_Chawda]

Left Join (Quotes)
Load
quote,
Supplier,
sum(
if(type='Parcel',
if(items=[Parcel Up to],
([Base Cost])+([Additional KG]*(if(payweight2>[Weight Finish],payweight2-[Weight Finish],0))),
(([Base Cost])+([Additional KG]*(if(ceil(payweight2/items,1)>[Weight Finish],ceil(payweight2/items,1)-[Weight Finish],0)))*[Parcel Up to])
+
(([Additional Parcel Base])+([Additonal Parcel KG]*(if(ceil(payweight2/items,1)>[Weight Finish],ceil(payweight2/items,1)-[Weight Finish],0))))*(items-[Parcel Up to])),
([Base Cost])+([Additional KG]*(if(payweight2>[Weight Finish],payweight2-[Weight Finish],0))))) as CalculatedCost
Resident Quotes

Group by quote,Supplier;

[MK_QSL]

Try

Left Join (Quotes)

Load

quote,

Supplier,

sum(

if(type='Parcel',

if(items=[Parcel Up to],

([Base Cost])+([Additional KG]*(if(payweight2>[Weight Finish],payweight2-[Weight Finish],0))),

(([Base Cost])+([Additional KG]*(if(ceil(payweight2/items,1)>[Weight Finish],ceil(payweight2/items,1)-[Weight Finish],0)))*[Parcel Up to])

+

(([Additional Parcel Base])+([Additonal Parcel KG]*(if(ceil(payweight2/items,1)>[Weight Finish],ceil(payweight2/items,1)-[Weight Finish],0))))*(items-[Parcel Up to])),

([Base Cost])+([Additional KG]*(if(payweight2>[Weight Finish],payweight2-[Weight Finish],0))))) as CalculatedCost

Resident Quotes

Group by quote, Supplier;