Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Contributor II

Using Round and division generate an error "Invalid expression"

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.

 

Labels (2)
1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

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

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

 

View solution in original post

5 Replies
Highlighted

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

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

Highlighted
Contributor II

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

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.

Highlighted
MVP & Luminary
MVP & Luminary

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

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

 

View solution in original post

Highlighted
Partner
Partner

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

Try with this expression : 

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

Highlighted
Contributor II

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

Hello @marcus_sommer , @StarinieriG ,

The expressions suggested return the expected result.

Thank you very much for your help.