Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Contributor III

How to use Group By Clause

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

Tags (1)
1 Solution

Accepted Solutions
Highlighted

Re: How to use Group By Clause

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;

View solution in original post

12 Replies
Highlighted

Re: How to use Group By Clause

What is your aggregation here?

Total_Sales_Year:

LOAD EmployeeID,

    Year,

    Sum(Sales)

Resident Orders

Group BY EmployeeID, Year;

Highlighted
Contributor III

Re: How to use Group By Clause

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

Highlighted

Re: How to use Group By Clause

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

Highlighted
Contributor III

Re: How to use Group By Clause

Now i got a synthetic key after loaded.

error.jpg

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

Rgds

Jim

Highlighted

Re: How to use Group By Clause

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

Highlighted
Contributor III

Re: How to use Group By Clause

concatenate will duplicate the records.

Highlighted

Re: How to use Group By Clause

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;

View solution in original post

Highlighted

Re: How to use Group By Clause

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

Highlighted
Contributor III

Re: How to use Group By Clause

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

Thanks bro Sunny!