topic Re: Using Round and division generate an error "Invalid expression" in QlikView

Using Round and division generate an error "Invalid expression"

Black_Hole — Sat, 16 Nov 2024 20:05:35 GMT

Hello all,

I meet an error "Invalid expression" with the following expression when I run my script.

[Table_00]:
LOAD
Item,
IF(Unit='TH',ROUND((SUM(Amount)/SUM(Quantity)),0.001)/1000,Amount) AS AmountStd
RESIDENT ARTICLE
GROUP BY Item;

Please could you tell me what's wrong with my expression.

Thank you in advance for your help.

Re: Using Round and division generate an error "Invalid expression"

sunny_talwar — Thu, 12 Sep 2019 10:26:30 GMT

Either Move if statement on Unit inside the Sum or Add it within your Group By clause.

Re: Using Round and division generate an error "Invalid expression"

Black_Hole — Thu, 12 Sep 2019 13:39:41 GMT

Hello @sunny_talwar ,

I modify my expression as you advised:

[Table_00]:
LOAD
Item,Unit
IF(Unit='TH',ROUND((SUM(Amount)/SUM(Quantity)),0.001)/1000,Amount) AS AmountStd
RESIDENT ARTICLE
GROUP BY Item, Unit;

But, I received again the same error message "Invalid expression".

Please could you help me to resolve this problem.

Thank you in advance for your help.

Re: Using Round and division generate an error "Invalid expression"

marcus_sommer — Thu, 12 Sep 2019 13:47:14 GMT

Your else-branch of the if contains with Amount a field which isn't aggregated and also not listed in the group by and therefore you get the syntax error. A bypass for this problem could be just to use two loads like:

[Table_00]:
LOAD Item, Unit
ROUND(SUM(Amount)/SUM(Quantity),0.001)/1000 AS AmountStd
RESIDENT ARTICLE where Unit ='TH' GROUP BY Item, Unit;

LOAD Item,Unit, Amount AS AmountStd
RESIDENT ARTICLE where where Unit <> 'TH';

- Marcus

Re: Using Round and division generate an error "Invalid expression"

StarinieriG — Thu, 12 Sep 2019 14:03:05 GMT

Try with this expression :

IF(Unit='TH',ROUND((SUM(Amount)/SUM(Quantity)),0.001)/1000,Sum(Amount)) AS AmountStd

Re: Using Round and division generate an error "Invalid expression"

Black_Hole — Thu, 12 Sep 2019 14:38:44 GMT

Hello @marcus_sommer , @StarinieriG ,

The expressions suggested return the expected result.

Thank you very much for your help.