Group by in qlikview and calculation on groupby

[#1]

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

[Frank_Hartmann]

try like this:

dimension:  Employee id

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

hope this helps

[Anonymous]

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)

[trdandamudi]

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)

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]

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;

[Anonymous · Accepted solution]

Hi John,

Use simple straight chart and apply following

Dimension:- Employee id

Expression:- Sum(orders)& '$'

you will get below table from that :

Employee id	Total Orders
1	200$
2	60$
3	500$

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 id	Total Orders
1	200$
2	60$
3	500$

Thanks,

[#8]

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]

what would be the expected output?

[#10]

can u place the qvw please