Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Preceding Load and Invalid Expression Error

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

9 Replies
MVP
MVP

Preceding Load and Invalid Expression Error

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

MVP
MVP

Preceding Load and Invalid Expression Error

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

Not applicable

Preceding Load and Invalid Expression Error

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

chauhans85
Esteemed Contributor

Preceding Load and Invalid Expression Error

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

MVP
MVP

Re: Preceding Load and Invalid Expression Error

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

Not applicable

Re: Preceding Load and Invalid Expression Error

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

MVP
MVP

Re: Preceding Load and Invalid Expression Error

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

MVP
MVP

Re: Preceding Load and Invalid Expression Error

Please look at attached sample.

Stefan

Not applicable

Re: Preceding Load and Invalid Expression Error

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

Community Browser