Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Creator III
Creator III

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
Master II
Master II

try like this:

dimension:  Employee id

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

hope this helps

lnimeshkrish
Contributor II
Contributor II

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

Anil_Babu_Samineni

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
Master II
Master II

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.

hemachandran
Partner
Partner

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
Creator III
Creator III

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
Author

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
Master II
Master II

what would be the expected output?

Not applicable
Author

can u place the qvw please