topic Re: Invalid Expression in QlikView

Invalid Expression

Wed, 25 Nov 2020 16:16:04 GMT

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;

Re: Invalid Expression

ankit777 — Thu, 23 Jun 2016 11:05:20 GMT

Are you missing group by in resident load?

Re: Invalid Expression

PrashantSangle — Thu, 23 Jun 2016 11:05:24 GMT

Sum() is aggregated function.

You need group by clause.

Regards

Re: Invalid Expression

Kushal_Chawda — Thu, 23 Jun 2016 11:07:23 GMT

Group by quote,Supplier;

Re: Invalid Expression

MK_QSL — Thu, 23 Jun 2016 11:09:51 GMT

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;