Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
Hi
Are you missing group by in resident load?
Sum() is aggregated function.
You need group by clause.
Regards
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;
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;