Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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,
try like this:
dimension: Employee id
expression: aggr(sum(orders),[employee name],sur_name,country,age)
hope this helps
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
Try like this
Dimension is employee id
Expression is
Sum(<[employee id]> total bond)
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.
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;
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,
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
what would be the expected output?
can u place the qvw please