Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
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

error message "invalid expression" while loading

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

Not applicable

error message "invalid expression" while loading

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

prieper
Honored Contributor II

error message "invalid expression" while loading

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

jsn
Honored Contributor

error message "invalid expression" while loading

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.

karunpreet
Contributor II

Re: error message "invalid expression" while loading

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

jsn
Honored Contributor

Re: error message "invalid expression" while loading

Hi,

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

Re: error message "invalid expression" while loading

Karunpreet Soni

Why would you group by 30 Columns.

That would gave your SQL statement additional headache.

Re: error message "invalid expression" while loading

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