Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the following load script:
LOAD Period,
[Project number],
Description,
Prof.Ctr. as [Profit Centre],
Typ as Type,
Projdef.,
[Ass. emp.],
Pers.No. as [Employee Number],
AutoNumber(Pers.No.) as EmployeeKey,
TRIM([Name of employee or applicant]) as Employee,
Prctr. as [Project Centre],
[Cost Ctr] as [Cost Centre],
PK,
BK,
Task,
Remark,
Status,
Link,
Crcy as Currency,
[Wage Type Long Text],
[Start pro.] as [Project start date],
[End date] as [Project end date],
Date,
[Created on],
[Changed on],
[Project time],
[T&D trv. time],
[Project time1],
[Planned hours],
FTE,
WTE,
Resource,
Cost,
Amount,
Area,
[Time Category],
[Time category group],
FYPeriod,
If([Time category group]= 'Billable', [Project time],
If([Time category group]= 'Productive', [Project time])) as BillProd
FROM
(
I want to obtain the sum of BillProd , divide it by the sum of [Project time] for each employee.
As a first step I tried:
LOAD *,
sum(BillProd) AS TotalBP
GROUP BY EmployeeKey;LOAD Period,
Etc..
This gives me an Invalid expression error. I imagine I need to do something with the Group By statement, but I am not sure what the determinates of Group By are.
Bryant
Hi,
you can't use a
LOAD *,
with a group by EmployeeKey clause.
You need to use an aggregation function for each field not listed in the group by.
Try removing the *, using maybe only EmployeeKey and your sum(BillProd) and then joing the resulting table back to your original table.
Hope this helps,
Stefan
Hi,
to be more clear,
I would first do a load of your original data, like
[Fact]:
LOAD
Period,
...
From ...;
Then I would try to add in the script a
Left Join (Fact) LOAD
EmployeeKey,
sum(BillProd) as TotalBP,
sum([Project Time]) as TotalPT,
sum(BillProd) / sum([Project Time]) as Avg
resident Fact group by EmployeeKey;
Regards,
Stefan
invalid expression error always caused by aggregrate funtion and group by
you need to include all field except used in sum function in Group by
ex:
Load
a
b
c,
sum(d) as d
resident path
gropu by a,b,c;
if u miss any of three (a,b,c)then it will cause invaalid expression
Bryant,
sorry, I am a bit slow today...
You want to sum(Red) per Month across Employees? Then maybe use another
RAG:
LOAD
Month,
sum(If(PercentageBP < 0.49999,1,0)) as SumRedMonth
Resident Projects group by Month;
If not, could you give a small example (as table here in the post or attached sample file)?
Sorry again,
how do you get the
(Smith 0.4) and (Jones 0.25) from your Input table? I either get 0.4 and 0.333 or 4/14 and 0.25, depending I I calculated Billable / Other or Billable / (Other+Billable).
Have you tried adding your Red calculation to the group by Employee, Month LOAD?
This should give you correct Reds per Employee and Months, shouldn't it?
regards,
Stefan
Please look at attached sample.
Stefan
Stefan,
Thanks for your reply, unfortunately it did not solve the problem. However, through trail and error (mainly error!) I managed to resolve the problem, so just in case you are interested I have pasted the amended Load below that stopped the duplicate rows:
Join (Projects)
LOAD EmployeeKey,
FYPeriod,
if (sum(BillProd)/sum([Project time]) < 0.49999,1,0) as Red
Resident Projects
GROUP BY EmployeeKey, FYPeriod;
Thanks once again for your help
Bryant