Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Group by in qlikview and calculation on groupby

Hi All

What does group by does

Will it consider set of  rows as 1 row

I have a situation where

Employee id,  employee name, age, sur_name, country, orders

1                     bond                     26    james        usa         200 $

3                     bond                      26   james        canada   500$     

2                      lisa                       22     ray            russia     20$

2                       lisa                       22     ray            russia     40$

My question is if   Employee id,  employee name, age, sur_name, country,   is same expect orders

consider it as one id  and the total orders is sum of both the rows ie 60 $.

if  any value in  Employee id,  employee name, age, sur_name, country,   is different consider it as different record

the output in the above senario is

Employee id   total orders

1                     200$

2                      60$

3                      500$

can we achieve it by any aggr function or group by in the script

1 Solution

Accepted Solutions
singhpooja
Contributor III

Re: Group by in qlikview and calculation on groupby

Hi John,

Use simple straight chart and apply following

Dimension:- Employee id

Expression:- Sum(orders)& '$'

you will get below table from that :

Employee idTotal Orders
1200$
260$
3500$

Else you can use aggregate function also as following:

Dimension:- Employee id

Expression:- aggr(Sum(orders)& '$',age,country,[employee name],sur_name)

you will get below table from that :

Employee idTotal Orders
1200$
260$
3500$

Thanks,

View solution in original post

16 Replies
Frank_Hartmann
Honored Contributor II

Re: Group by in qlikview and calculation on groupby

try like this:

dimension:  Employee id

expression:  aggr(sum(orders),[employee name],sur_name,country,age)

hope this helps

lnimeshkrish
New Contributor II

Re: Group by in qlikview and calculation on groupby

Hi,

I believe if you create a simple report with Employee id and total Orders, you will receive it.

Yes, you can group by in script, please see the below.

Load Employee id, Employee name,age,sur_name,country , sum(total ordere)

From File

Group by Employee id, Employee name,age,sur_name,country;

Regards,

Nimesh Krishnan L

Re: Group by in qlikview and calculation on groupby

Try like this

Dimension is employee id

Expression is

Sum(<[employee id]> total bond)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
trdandamudi
Honored Contributor II

Re: Group by in qlikview and calculation on groupby

You can do this in couple of ways as below:

Option I:

Just load your data and create a straight table as below:

Dimension:  Employee_Id

Expression: Sum(Orders)

GroupBy.jpg

Option II:

If you want to use group by in the script then follow as below:

Source_Data:

Load [Employee_id], [Employee name],[age],[sur_name],[country], sum(orders)

Group by [Employee_id], [Employee name],age,sur_name,country;

LOAD * INLINE [

Employee_id,  Employee name, age, sur_name, country, orders

1,                    bond,                    26,    james,        usa,        200

3,                    bond,                      26,  james,        canada,    500  

2,                      lisa,                      22,    ray,            russia,    20

2,                      lisa,                      22,    ray,            russia,  40

];

Note: You can use group by only on the Resident table.

Partner
Partner

Re: Group by in qlikview and calculation on groupby

Hi cena,

You can do it in script itself

Load

   Employee id,

  employee name,

   age,

   sur_name,

  country,

  orders,

  sum(orders) as TotalOrders

  resident Table name

group by

Employee id, employee name,age,sur_name,country,orders;

Drop Table Table name;


singhpooja
Contributor III

Re: Group by in qlikview and calculation on groupby

Hi John,

Use simple straight chart and apply following

Dimension:- Employee id

Expression:- Sum(orders)& '$'

you will get below table from that :

Employee idTotal Orders
1200$
260$
3500$

Else you can use aggregate function also as following:

Dimension:- Employee id

Expression:- aggr(Sum(orders)& '$',age,country,[employee name],sur_name)

you will get below table from that :

Employee idTotal Orders
1200$
260$
3500$

Thanks,

View solution in original post

Not applicable

Re: Group by in qlikview and calculation on groupby

Hi Frank,

what about the count, if i want to calculate the count of employee id based on the same senario, its not giving me correct results can you pls guide me

Frank_Hartmann
Honored Contributor II

Re: Group by in qlikview and calculation on groupby

what would be the expected output?

Not applicable

Re: Group by in qlikview and calculation on groupby

can u place the qvw please