Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Error Invalid expression in a Group By

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.

1 Solution

Accepted Solutions
Highlighted
Creator III
Creator III

Re: Error Invalid expression in a Group By

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;

View solution in original post

4 Replies
Highlighted
Creator III
Creator III

Re: Error Invalid expression in a Group By

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;

View solution in original post

Highlighted
Partner
Partner

Re: Error Invalid expression in a Group By

Hi

 

you have to add NewRate inside group by

Highlighted
Creator
Creator

Re: Error Invalid expression in a Group By

[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
Creator II
Creator II

Re: Error Invalid expression in a Group By

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.