Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
There is also an option to use ApplyMap, but I don't think there is going to be much performance difference.
hey bro,
mind to tell me a lil bit a definition of Group By , and when should we use Group By???
Rgds
Jim
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.