topic Re: group by in QlikView

group by

manoranjan_d — Thu, 29 Jan 2015 11:57:07 GMT

can you explain group by function in qlikview. since i'm beginner

Re: group by

manoranjan_d — Thu, 29 Jan 2015 11:57:44 GMT

with example data of two fields grouped

Re: group by

Thu, 29 Jan 2015 12:00:35 GMT

Hi Manoranjan,

if you do a search on the community, you will find this question has been asked and answered a number of times

you'll also find the QV reference manual has an explanation of this too

hope that helps

Joe

Re: group by

agomes1971 — Thu, 29 Jan 2015 12:05:46 GMT

Hi,

directly from help!

group by is a clause used for aggregating (group) several records into one. Within one group, for a certain field, all the records must either have the same value, or the field can only be used from within an expression, e.g. as a sum or an average. The expression based on one or several fields is defined in the expression of the field symbol.

HTH

André Gomes

Re: group by

Gabriel — Thu, 29 Jan 2015 12:10:05 GMT

Hi,

Tab1:

Select * from Trans;

Load

Month

, Sum(Sales)

resident Tab1

group by Month;

You must not include anything that's not aggregated, as in anything not wrapped in SUM, COUNT, AVG, E.T.C

Best Regards,

Gabriel

Re: group by

robert_mika — Thu, 29 Jan 2015 12:15:33 GMT

If you are referring to GROUP BY function from Load statement

Add this into Edit Script window and Reload:

Table1:

Load * inline

[

UID,ID,NULLCOLUMN,DATE,DATE-1workday

1,1,a,11-12-2013,10-12-2013

2,1,,15-11-2013,14-11-2013

3,1,b,07-10-2014,06-10-2014

4,2,,23-11-2012,21-11-2012

5,2,0,07-07-2012,06-07-2012

6,3,0,14-01-2015,13-01-2015

7,4,0,30-08-2014,27-08-2014

8,4,0,15-05-2013,14-05-2013

];

Table2:

Load

ID as ID2, count(UID) as IID

resident Table1

GROUP BY ID,UID

Re: group by

Anonymous — Thu, 29 Jan 2015 12:21:35 GMT

Hi,

The GROUP BY statement is used in conjunction with the aggregate functions to group the Data by one or more columns.

Ex:

Load
A,
Sum(B) as B,
Max(Date) as Date,
D on
From Table
Group by A,D;

Fields used in aggregations should not be used in same statement again else users will get invalid expression You must not include fields aggregate in group by.

Try to avoid fields in load statement which are not included in group by

Regards

Neetha

Re: group by

manoranjan_d — Thu, 29 Jan 2015 15:17:45 GMT

Understood and practiced with example data.

Re: group by

manoranjan_d — Thu, 29 Jan 2015 15:17:53 GMT

thanks all

Re: group by

Gabriel — Thu, 29 Jan 2015 15:31:13 GMT

Kindly mark Answer or helpful