Using Round and division generate an error "Invalid expression"

[Black_Hole]

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.

[sunny_talwar]

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

[Black_Hole]

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.

[marcus_sommer · Accepted solution]

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

[StarinieriG]

Try with this expression : 

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

[Black_Hole]

Hello @marcus_sommer , @StarinieriG ,

The expressions suggested return the expected result.

Thank you very much for your help.