topic Re: Error Invalid expression in a Group By in QlikView

Error Invalid expression in a Group By

Black_Hole — Sat, 16 Nov 2024 20:04:16 GMT

Hello all,

I receive an error message "Invalid expression" when I run the following script.

[TABLE_2]:
LOAD
ID_Item,
IF(SUM(Sales)=0 AND SUM(Movements)=0 AND NewRate='>=365','90%',
IF(SUM(Sales)>0 OR SUM(Movements)>0,' ',
IF(SUM(Sales)=0 AND SUM(Movements)>0) ,ROUND(FABS(SUM(QtyDeb)-SUM(QtyFin))/SUM(QtyDeb),0.01)))) AS NewRateBis
RESIDENT TABLE_1
GROUP BY ID_Item;

I think that this error is due to the expression "New='>=365'".

But, I don't know how I can in order to resolve this problem.

Please could you help me.

Many thanks in advance for your help.

Re: Error Invalid expression in a Group By

asinha1991 — Tue, 17 Sep 2019 10:26:17 GMT

two ways

1. Aggregate NewRate if it is not unique for ID_Item

LOAD
ID_Item,
IF(SUM(Sales)=0 AND SUM(Movements)=0 AND maxString(NewRate)='>=365','90%',
IF(SUM(Sales)>0 OR SUM(Movements)>0,' ',
IF(SUM(Sales)=0 AND SUM(Movements)>0) ,ROUND(FABS(SUM(QtyDeb)-SUM(QtyFin))/SUM(QtyDeb),0.01)))) AS NewRateBis
RESIDENT TABLE_1
GROUP BY ID_Item;

2. Add it in group by if it is unique

LOAD
ID_Item,
IF(SUM(Sales)=0 AND SUM(Movements)=0 AND NewRate='>=365','90%',
IF(SUM(Sales)>0 OR SUM(Movements)>0,' ',
IF(SUM(Sales)=0 AND SUM(Movements)>0) ,ROUND(FABS(SUM(QtyDeb)-SUM(QtyFin))/SUM(QtyDeb),0.01)))) AS NewRateBis
RESIDENT TABLE_1
GROUP BY ID_Item,NewRate;

Re: Error Invalid expression in a Group By

StarinieriG — Tue, 17 Sep 2019 10:26:43 GMT

you have to add NewRate inside group by

Re: Error Invalid expression in a Group By

sugathirajkumar — Tue, 17 Sep 2019 10:31:50 GMT

[TABLE_2]:
LOAD
ID_Item,
IF(SUM(Sales)=0 AND SUM(Movements)=0 AND NewRate='>=365','90%',
IF(SUM(Sales)>0 OR SUM(Movements)>0,' ',
IF(SUM(Sales)=0 AND SUM(Movements)>0 ,
ROUND(FABS(SUM(QtyDeb)-SUM(QtyFin))/SUM(QtyDeb),0.01)))) AS NewRateBis
RESIDENT TABLE_1
GROUP BY ID_Item;

Take that ) next to 0

Re: Error Invalid expression in a Group By

Black_Hole — Tue, 17 Sep 2019 10:43:50 GMT

Hello @asinha1991,@StarinieriG , @sugathirajkumar ,

Many thanks everyone for your quick reply.

So, I try it by adding NewRate in the Group By and it works perfectly.

@sugathirajkumar :

I don't understand what you mean by "Take that ) next to 0".

Please could you put in bold the part of my script to modify.

Thank you in advance.