Preceding Load and Invalid Expression Error

[#1]

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

(ooxml, embedded labels, table is [Utilisation]);

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

[swuehl]

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

[swuehl]

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

[#4]

Stefan,

Thanks for your response. I subsequently found that you cannot do a Group By on a preceding load.
I have tried using a Join to create what I want, but I am still having trouble with my Group By’s
I have added the Following:

Percentage:
Join (Projects)
LOAD DISTINCT
EmployeeKey,
Month,
sum(BillProd)/sum([Project time]) AS PercentageBP
Resident Projects
GROUP BY EmployeeKey, Month;

RAG:
LOAD DISTINCT
EmployeeKey,
If(PercentageBP < 0.49999,1,0) as Red
Resident Projects;

If I Group by EmployeeKey only I cannot divide my results by the Month. If I add Month to the group by I get multiple answers for every Employee and the subsequent RAG calculation results in :
-all employees that only months with one will have one Red entry per month
-All employees with some month 1 and others 0 will have a 1 and a 0 Red entry for every month
-all employees that only months with zero will have one Red entry per month
I would like to do sum(Red) per month but I can’t!!
Am I missing something obvious?
Bryant

[SunilChauhan]

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

[swuehl]

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)?

[#7]

Hi Stefan,

I think I have confused the issue.
A simplified version of my input table
Month, Employee, Time, Category Group, Project Time
1 Smith Billable 4
1 Smith Other 10
2 Smith Billable 7
1 Jones Billable 9
1 Jones Other 2
2 Jones Billable 1
2 Jones Other 3
3 Jones Billable 12
3 Smith Billable 11

I want to calculate the count of employees in ‘Red’ for each month
Month 1 – 1 in Red (Smith 0.4)
Month 2 – 1 in Red (Jones 0.25)
Month 3 – 0 in Red
(My current script would give me a 0 and 1 Red flag for both Smith and Jones for each month which is obviously wrong)

My script is as follows:

Projects:
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],
Month,
If([Time category group]= 'Billable', [Project time],
If([Time category group]= 'Productive', [Project time])) as BillProd

FROM

(ooxml, embedded labels, table is [Utilisation]);

Join (Projects)
LOAD DISTINCT
EmployeeKey,
Month,
sum(BillProd)/sum([Project time]) AS PercentageBP
Resident Projects
GROUP BY EmployeeKey, Month;


RAG:
LOAD DISTINCT
EmployeeKey,
If(PercentageBP < 0.49999,1,0) as Red
Resident Projects;

But it does not give me the answers I want
Thanks for your help
Bryant

[swuehl]

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

[swuehl]

Please look at attached sample.

Stefan

[#10]

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