topic Re: How to use Group By Clause in QlikView

How to use Group By Clause

jim_chan — Sun, 18 Sep 2016 09:56:30 GMT

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

Re: How to use Group By Clause

sunny_talwar — Sun, 18 Sep 2016 09:58:54 GMT

What is your aggregation here?

Total_Sales_Year:

LOAD EmployeeID,

Year,

Sum(Sales)

Resident Orders

Group BY EmployeeID, Year;

Re: How to use Group By Clause

jim_chan — Sun, 18 Sep 2016 10:00:02 GMT

ooooh. , i need to create an anggregation when i use group by clause???

Re: How to use Group By Clause

sunny_talwar — Sun, 18 Sep 2016 10:01:20 GMT

Yes, why else would you want to use a Group by statement?

Re: How to use Group By Clause

jim_chan — Sun, 18 Sep 2016 10:02:37 GMT

Now i got a synthetic key after loaded.

Can i create a composite key? and add it on top ??

Rgds

Jim

Re: How to use Group By Clause

sunny_talwar — Sun, 18 Sep 2016 10:06:10 GMT

‌May be left join this into Orders table or combine EmployeeId and Year to create a concatenated key

Re: How to use Group By Clause

jim_chan — Sun, 18 Sep 2016 10:09:19 GMT

concatenate will duplicate the records.

Re: How to use Group By Clause

sunny_talwar — Sun, 18 Sep 2016 10:09:47 GMT

Option 1:

Orders:

LOAD EmployeeID,

Year,

Sales,

....

FROM Orders;

Left Join (Orders)

LOAD EmployeeID,

Year,

Sum(Sales)

Resident Orders

Group By EmployeeID, Year;

Option 2:

Orders:

LOAD EmployeeID,

Year,

AutoNumber(EmployeeID&Year, 'Total_Sales_Year') as Key,

Sales,

....

FROM Orders;

Total_Sales_Year:

LOAD Key,

Sum(Sales)

Resident Orders

Group By Key;

Re: How to use Group By Clause

sunny_talwar — Sun, 18 Sep 2016 10:10:16 GMT

Look at my response below.... I have provided two options

Re: How to use Group By Clause

jim_chan — Sun, 18 Sep 2016 10:13:21 GMT

i have used option 1 before you posted up. yes. left join seems working fine, bro.

Thanks bro Sunny!

Re: How to use Group By Clause

sunny_talwar — Sun, 18 Sep 2016 10:15:13 GMT

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

Re: How to use Group By Clause

jim_chan — Sun, 18 Sep 2016 10:21:05 GMT

hey bro,

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

Rgds

Jim

Re: How to use Group By Clause

sunny_talwar — Sun, 18 Sep 2016 10:33:32 GMT

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.