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

[#2]

Hi Vincent, I used the same; but still if EmpID is more than 1 time in the table its showing the data.

>>Henric: I have used EmpIDs=1 in the preceeding load

[vincent_ardiet]

Are you sure this is not comming from the data ?

When you see an EmpID twice, is it with the same value in the folowing columns :

SOID,SONAME,ProjectID,AssignmentStartDate,AssignmentEndDate,FactID

Because, as they are used in your GROUP BY, they must be the same. Else you will count the number of time that the combinaison of all (EmpID,SOID,SONAME,ProjectID,AssignmentStartDate,AssignmentEndDate,FactID) is appearing in your datasource.

[IAMDV]

Please can you upload the sample QV file? It helps us to look at the data.

Thanks,

DV

[hic]

If you have a where clause filtering out lines with EmpIDs=1, then you must have the same EmpID on several lines. You have a group by clause that looks like

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

Then you most likely have the same EmpID in several ProjectID (or SOID, FactID or combination of the group by fields). What do you want to do with these? Just loading one of the projects if the employee is linked to several does not make sense, or?

HIC

[#6]

Please find the QV and the sample data attached.

[vincent_ardiet]

Try this :

Table1:
LOAD
  EmpId,
     ProjectId,
     AssignmentStartDate,
     AssignmentEndDate,
     SoId,
     SoLine
FROM HD:\SampleData.xls (biff, embedded labels, table is Sheet3$) ;

TbCounter:
LOAD
EmpId, count(1) as Counter
Resident Table1
Group By EmpId ;

Inner Join (Table1) Load
EmpId
Resident TbCounter
Where Counter=1 ;

Drop Table TbCounter ;

Regards,

Vincent

[#8 · Accepted solution]

Here send you your Having Test.qvs modified, hope this is wath you need.

Regards.-

[#9]

Thank You all for your help:)