I have the following load script:
Prof.Ctr. as [Profit Centre],
Typ as Type,
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],
Crcy as Currency,
[Wage Type Long Text],
[Start pro.] as [Project start date],
[End date] as [Project end date],
[T&D trv. time],
[Time category group],
If([Time category group]= 'Billable', [Project time],
If([Time category group]= 'Productive', [Project time])) as BillProd
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:
sum(BillProd) AS TotalBP
GROUP BY EmployeeKey;LOAD Period,
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.
you can't use a
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,
to be more clear,
I would first do a load of your original data, like
Then I would try to add in the script a
Left Join (Fact) LOAD
sum(BillProd) as TotalBP,
sum([Project Time]) as TotalPT,
sum(BillProd) / sum([Project Time]) as Avg
resident Fact group by EmployeeKey;
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
sum(d) as d
gropu by a,b,c;
if u miss any of three (a,b,c)then it will cause invaalid expression
sorry, I am a bit slow today...
You want to sum(Red) per Month across Employees? Then maybe use another
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)?
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?
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:
if (sum(BillProd)/sum([Project time]) < 0.49999,1,0) as Red
GROUP BY EmployeeKey, FYPeriod;
Thanks once again for your help