topic Re: having clause with count in QlikView

having clause with count

Fri, 04 May 2012 07:48:51 GMT

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

having clause with count

hic — Fri, 04 May 2012 08:04:07 GMT

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

Re: having clause with count

Fri, 04 May 2012 08:10:55 GMT

If I remove its not showing any data...

Re: having clause with count

Fri, 04 May 2012 08:24:39 GMT

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

having clause with count

hic — Fri, 04 May 2012 08:26:19 GMT

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

HIC

Re: having clause with count

Fri, 04 May 2012 08:27:59 GMT

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

Re: having clause with count

Fri, 04 May 2012 08:35:46 GMT

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

Re: having clause with count

hic — Fri, 04 May 2012 08:54:58 GMT

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

HIC

having clause with count

Fri, 04 May 2012 08:56:03 GMT

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

Re: having clause with count

vincent_ardiet — Fri, 04 May 2012 09:20:04 GMT

Hi,

Just replace in your code:

count(EmpID) as EmpIDs

by:

count(1) as EmpIDs

And it will work.

Regards,

Vincent

Re: having clause with count

Fri, 04 May 2012 09:28:51 GMT

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

Re: having clause with count

vincent_ardiet — Fri, 04 May 2012 09:44:08 GMT

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.

Re: having clause with count

IAMDV — Fri, 04 May 2012 09:47:44 GMT

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

Thanks,

Re: having clause with count

hic — Fri, 04 May 2012 09:50:03 GMT

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

Re: having clause with count

Fri, 04 May 2012 10:10:49 GMT

Please find the QV and the sample data attached.

Re: having clause with count

vincent_ardiet — Fri, 04 May 2012 11:33:02 GMT

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

Re: having clause with count

Fri, 04 May 2012 11:41:54 GMT

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

Regards.-

having clause with count

Fri, 04 May 2012 12:08:05 GMT

Thank You all for your help:)