topic error message "invalid expression" while loading in QlikView

error message "invalid expression" while loading

Mon, 23 Nov 2009 01:40:03 GMT

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...

error message "invalid expression" while loading

Mon, 23 Nov 2009 02:15:53 GMT

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

error message "invalid expression" while loading

Mon, 23 Nov 2009 02:19:53 GMT

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

error message "invalid expression" while loading

prieper — Mon, 23 Nov 2009 03:48:15 GMT

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

error message "invalid expression" while loading

Anonymous — Mon, 23 Nov 2009 11:39:00 GMT

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.

Re: error message "invalid expression" while loading

Anonymous — Thu, 15 May 2014 13:33:27 GMT

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

Re: error message "invalid expression" while loading

Anonymous — Fri, 16 May 2014 03:00:01 GMT

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

robert_mika — Fri, 16 May 2014 04:24:23 GMT

Karunpreet Soni

Why would you group by 30 Columns.

That would gave your SQL statement additional headache.

Re: error message "invalid expression" while loading

MayilVahanan — Fri, 16 May 2014 04:51:21 GMT

Split the query like this

TableA:

Load Uniqueid, field1, field2 from table1;

join(TalbeA)

Load Uniqueid, Sum(field3) resident TableA;

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

Hope it helps