having clause with count

[#1]

I have a script, where I am loading the count of Employees in the preceeding load. Even if the EmpId exists 2 or more times it shows the count as 1 only. I want to have the count of employees as 2 if there are 2records of that employee in the table.

LOAD *

WHERE EmpIDs = 1;

LOAD EmpID, count(EmpID) as EmpIDs, SOID,SONAME,ProjectID,AssignmentStartDate,AssignmentEndDate,FactID

RESIDENT OrderTable

WHERE AssignmentStartDate >= 20110101 and AssignmentEndDate <= 20120101

GROUP BY EmpID,SOID,SONAME,ProjectID,AssignmentStartDate,AssignmentEndDate,FactID

[hic]

The count of EmpID will always be 1 if you have EmpID in the group by clause. So, remove it from the group by clause and it will work.

/HIC

[#3]

If I remove its not showing any data...

[#4]

Is the count of employees to be finally shown in chart front end?

[hic]

You need to replace the "EmpID" with "Only(EmpID) as EmpID" in the list of loaded fields, also.

HIC

[#6]

Robin: yes, I just want to show the count in a text if the EmpID exists only one time in the table.

[#7]

Henric: Yes it worked, but still I am getting employees whose entries are two times in the table.

[hic]

Even though you have a preceding load with a where clause demanding EmpIDs=1 ?

HIC

[#9]

From what i understand, can u just load the table plain as:

LOAD EmpID, EmpID, SOID,SONAME,ProjectID,AssignmentStartDate,AssignmentEndDate,FactID

RESIDENT OrderTable

WHERE AssignmentStartDate >= 20110101 and AssignmentEndDate <= 20120101;

\and then simply use count(distinct EmpID)?

Thanks,

Robinson

[vincent_ardiet]

Hi,

Just replace in your code:

count(EmpID) as EmpIDs

by:

count(1) as EmpIDs

And it will work.

Regards,

Vincent