topic Re: groupby and orderby clause in qlikview in QlikView

groupby and orderby clause in qlikview

tripatirao — Mon, 14 Sep 2015 08:13:49 GMT

Hi guys

I want to know use of group by and order by clause in qlikview.

thanks in advance

Re: groupby and orderby clause in qlikview

MK_QSL — Mon, 14 Sep 2015 08:17:38 GMT

Sales:

Load * Inline

[

Customer, Year, Sales

A, 2014, 100

A, 2015, 120

B, 2014, 200

B, 2015, 220

];

Final:

Load

Customer,

SUM(Sales) as TotalSales

Resident Sales

Group By Customer;

Drop Table Sales;

For Order By Use below link..

Count if value does not exist in all previous rows

Re: groupby and orderby clause in qlikview

manojkulkarni — Mon, 14 Sep 2015 08:49:36 GMT

Group by - this will be used when you want to get an aggregated data on specific columns.

Order By - will be used to order your data on certain columns either in desc or ascending.

Re: groupby and orderby clause in qlikview

tripatirao — Mon, 14 Sep 2015 09:57:41 GMT

Hi manish

thanks for quick response.Can we use groupby and order by clause together in a script.

Re: groupby and orderby clause in qlikview

avinashelite — Mon, 14 Sep 2015 10:02:57 GMT

Yes , you can use both group by and order by together

Re: groupby and orderby clause in qlikview

tripatirao — Mon, 14 Sep 2015 10:07:20 GMT

Hi Avinash

Can you provide one exemple fot it.

Re: groupby and orderby clause in qlikview

Kushal_Chawda — Mon, 14 Sep 2015 10:15:20 GMT

Data:

LOAD * Inline [

CustomerName, ID, Amount

A,1,100

A,2,200

B,1,250,

B,2,130 ];

New:

NoConcatenate

LOAD CustomerName,

Count(ID) as Count,

sum(Amount) as Amount

Resident Data

Group by CustomerName

Order by CustomerName;

DROP Table Data;

see the attached

Re: groupby and orderby clause in qlikview

avinashelite — Mon, 14 Sep 2015 10:15:27 GMT

Temp:

LOAD * inline

[

ID,Amount

10,12

6,26,

4,20

1,200

1,300

2,100

3,150

];

LOAD ID as NEw_ID,

sum(Amount) as Total

Resident

Temp

Group by ID Order By ID;

Re: groupby and orderby clause in qlikview

Anonymous — Mon, 14 Sep 2015 10:22:10 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

The ORDER BY statement orders data ASC/DESC.by default its ascending

//ASC

LOAD

Key,

Year,

Month,

ID,

Job

RESIDENT Temp1

ORDER BY ID, Year, Month;

//DESC

LOAD

Key,

Year,

Month,

ID,

Job

RESIDENT Temp1

ORDER BY ID, Year, Month DESC ;//month is ordered descending.