Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

error message "invalid expression" while loading

Hello,

When executing the following code:

ElectricityDaily:

LOAD Date,
ShortDate,
Type_ID,
MonitoringPoint_ID,
sum(Value_E) as Value_E_Daily,
Unit_ID
FROM [QVDs\Data.qvd] (qvd)
GROUP BY ShortDate;




I get the error "Invalid expression"

I have no idea what the problem is...

8 Replies
Not applicable
Author

I think it is because Date, Type_ID, etc... are not mentioned in the group by statement.

Not applicable
Author

But I only want to sum across Date_Short... do I have to include all the fields in the group by?!?!

prieper
Master II
Master II

Depending, what you need to see: if the grouping fullfils your requirement, go for it. If not, you may look for First/Last Sorted Value or Min/Max String.

Peter

Anonymous
Not applicable
Author

Yes, you will need to use group by over all fields with no aggregation, or separate the load statement.

Imagine the following:

Year Unit Sales
2008 A 5000
2008 B 8000
2009 A 6000
2009 B 5000


Now if you want to sum up the Sales and group it by Year you will end up with:

Year Sum(Sales)
2008 13000
2009 11000


This looks ok and is what we would expect. If we also added the Unit and did not group by the Unit value, what would we expect to see?

Year Unit Sum(Sales)
2008 A 13000
2008 B 13000
2009 A 11000
2009 B 11000


This is obviously wrong so you have to group by the fields that are not part of an aggregation. Or just load Date_Short and your aggregation in one table and you can either join that to the table one way or another or keep it separated and linked with Date_Short as the key field.

Anonymous
Not applicable
Author

hey jsn

i have 30 columns in my table, so do i need to write all the 30 names in the group by clause, except the column names which i m using with my count or sum function

Anonymous
Not applicable
Author

Hi,

That is correct. You need to specify all fields in your group by clause that are not part of an aggregation.

robert_mika
Master III
Master III

Karunpreet Soni

Why would you group by 30 Columns.

That would gave your SQL statement additional headache.

MayilVahanan

HI

Split the query like this

TableA:

Load Uniqueid, field1, field2 from table1;

join(TalbeA)

Load Uniqueid, Sum(field3) resident TableA;

or

Create the Key from table1 and by using that key, do Join operation.

Hope it helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.