How to use Group By Clause

[jim_chan]

Hi guys,

i wanted to use Group By Clause. my script is as below. It didnt load succerssfully. error is "Aggregation expressions required by GROUP BY clause".

What have been missing in this table?

Total_Sales_Year:

load

EmployeeID,

Year

Resident Orders

Group BY EmployeeID,Year;

Rgds,

Jim

[sunny_talwar]

There is also an option to use ApplyMap, but I don't think there is going to be much performance difference.

[jim_chan]

hey bro,

mind to tell me a lil bit a definition of Group By , and when should we use Group By???

Rgds

Jim

[sunny_talwar]

I am not sure if there is a good definition of Group By, but the main idea is that if you are looking to aggregate the data -> Find Sum, Avg, Min, Max, and so on in the script you can use Group By to do this. For example, lets take a very simple table

Dim1	Dim2	Value
A	AA	10
A	AA	20
A	AB	30
A	AC	40
B	AA	50
B	AC	20
B	AC	30

And I don't want to just Sum(Value) by Dim1 and Dim2. Row 1 and Row 2 doesn't really provide me extra details for some reason and I want to just combine there values into one row. I can now use Group By statement

LOAD Dim1,

     Dim2,

     Sum(Value) as Value

Resident ....

Group By Dim1, Dim2;

and now you will get this:

Dim1	Dim2	Value
A	AA	30
A	AB	30
A	AC	40
B	AA	50
B	AC	50

You can further read here:

https://qlikcentral.com/2014/05/15/aggregation-for-beginners/‌

One thing to note: you will always need to use non-aggregating fields in your group by statement, or else you will run into syntax errors while reloading your script.