Error Invalid expression in a Group By

[Black_Hole]

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.

[asinha1991 · Accepted solution]

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;

[StarinieriG]

Hi

you have to add NewRate inside group by

[sugathirajkumar]

[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

[Black_Hole]

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.