groupby and orderby clause in qlikview

[tripatirao]

Hi guys

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

thanks in advance

[MK_QSL · Accepted solution]

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

[manojkulkarni]

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.

[tripatirao]

Hi manish

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

[avinashelite]

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

[tripatirao]

Hi Avinash

Can you provide one exemple fot it.

[Kushal_Chawda]

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

[avinashelite]

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;

[Anonymous]

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.