GROUP BY requires ALL fields?

[pennetzdorfer]

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!

[#2 · Accepted solution]

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.

[jonathandienst]

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

[pennetzdorfer]

hey michelkr,

thank you, that makes sense!

[#5]

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]

Jonathan, thanks a lot for you explanations!