Discussion Board for collaboration related to Creating Analytics for QlikView.
When executing the following code:
sum(Value_E) as Value_E_Daily,
FROM [QVDs\Data.qvd] (qvd)
GROUP BY ShortDate;
I get the error "Invalid expression"
I have no idea what the problem is...
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.
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:
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.
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
Split the query like this
Load Uniqueid, field1, field2 from table1;
Load Uniqueid, Sum(field3) resident TableA;
Create the Key from table1 and by using that key, do Join operation.
Hope it helps