Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

pennetzdorfer
Contributor III

GROUP BY requires ALL fields?

Hello,

I want to aggregate daily Sales for each Country.

load

     Date,
     Year,
     Quarter,
     Month,
     Week,
     Day,
     Country,
     Sum(Sales)
resident SalesTable group by Date, Country;

Running the script results in an "Invalid expression" error message... apparently I have to list ALL used fields in the group by clause

Qlikview Manual:

No other fields than the aggregation fields may be used outside aggregation functions in the loaded expressions.

If I do so, it works. But in my opinion this is illogical: Why do I have to list Year, Quarter, Month, Week and Day in the group by clause although they don't affect the aggregation level (Date = lowest level of detail)?!

In my real application the group by clause would then consist of about 20 fields ... is there another way to avoid that?

Thx for you suggestions!

Tags (2)
1 Solution

Accepted Solutions
Not applicable

Re: GROUP BY requires ALL fields?

Try this:

load     Date,
     only(Year),
     only(Quarter),
     only(Month),
     only(Week),
     only(Day),
     Country,
     Sum(Sales)
resident SalesTable group by Date, Country;

You as a person does understand that the date column is the lowest level of detail, but as far as QlikView concerns, the other fields (such as year, etc) do not have to be relate to the date field. (QlikView does not know that each date can only have one year, etc).

You could also decide not to load the other fields, but just the date field.

5 Replies
Not applicable

Re: GROUP BY requires ALL fields?

Try this:

load     Date,
     only(Year),
     only(Quarter),
     only(Month),
     only(Week),
     only(Day),
     Country,
     Sum(Sales)
resident SalesTable group by Date, Country;

You as a person does understand that the date column is the lowest level of detail, but as far as QlikView concerns, the other fields (such as year, etc) do not have to be relate to the date field. (QlikView does not know that each date can only have one year, etc).

You could also decide not to load the other fields, but just the date field.

MVP
MVP

Re: GROUP BY requires ALL fields?

Florian

The load statement has no knowledge of the relationship between Date and Year/Quarter/Month/Week fields, so it would not know how to fulfill them.

As in SQL with a Group By, the fields must be in either an aggregate expression or listed in the Group By clause. And no, there is no way round that. You could include them in the statement as aggregates like Max(Year) As Year, etc, but that is more work than simply listing the fields in the Group By. I just copy the field list from the Load to the Group By, and delete the aggregate fields.

In your example, because Year etc are indeed dependant on Date, including them in the Group By will not change the results.

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
pennetzdorfer
Contributor III

Re: GROUP BY requires ALL fields?

hey michelkr,

thank you, that makes sense!

Not applicable

Re: GROUP BY requires ALL fields?

You're welcome, glad i could help

As Jonathan also mentions, SQL will also require to OR have the fields in the group by clause, OR use a aggregation function.

pennetzdorfer
Contributor III

Re: GROUP BY requires ALL fields?

Jonathan, thanks a lot for you explanations!