topic Re: GROUP BY requires ALL fields? in QlikView

GROUP BY requires ALL fields?

pennetzdorfer — Wed, 29 Aug 2012 09:40:15 GMT

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!

Re: GROUP BY requires ALL fields?

Wed, 29 Aug 2012 09:51:22 GMT

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.

Re: GROUP BY requires ALL fields?

jonathandienst — Wed, 29 Aug 2012 09:56:49 GMT

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

Re: GROUP BY requires ALL fields?

pennetzdorfer — Wed, 29 Aug 2012 09:58:29 GMT

hey michelkr,

thank you, that makes sense!

Re: GROUP BY requires ALL fields?

Wed, 29 Aug 2012 10:01:28 GMT

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.

Re: GROUP BY requires ALL fields?

pennetzdorfer — Wed, 29 Aug 2012 10:43:42 GMT

Jonathan, thanks a lot for you explanations!