Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

having clause in Qlikview

Hi,

Is there any function similar to Having Clause of Sql Statement.

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

Just use a preceeding load for your HAVING conditions. So something like this:

SELECT Customer, count(Order) as Orders, sum(Sales) as Sales
FROM OrderTable
WHERE OrderDate >= 20100101
GROUP BY Customer
HAVING count(Order)>10

Would be written like this:

LOAD *
WHERE Orders > 10
;
LOAD Customer, count(Order) as Orders, sum(Sales) as Sales
RESIDENT OrderTable
WHERE OrderDate >= 20100101
GROUP BY Customer
;

View solution in original post

11 Replies
gandalfgray
Specialist II
Specialist II

No, unfortunately not, you have to make a workaround, for example by creating a temporary table.

johnw
Champion III
Champion III

Just use a preceeding load for your HAVING conditions. So something like this:

SELECT Customer, count(Order) as Orders, sum(Sales) as Sales
FROM OrderTable
WHERE OrderDate >= 20100101
GROUP BY Customer
HAVING count(Order)>10

Would be written like this:

LOAD *
WHERE Orders > 10
;
LOAD Customer, count(Order) as Orders, sum(Sales) as Sales
RESIDENT OrderTable
WHERE OrderDate >= 20100101
GROUP BY Customer
;

Not applicable
Author

Thanks John,

I was looking for the Having clause option in the load statement when the data is loaded from excel or CSV or some text files.

johnw
Champion III
Champion III

You would use the exact same technique for Excel, CSV, text files, inline loads, resident loads, or any other data source you can use in QlikView. It doesn't matter what the second load is as long as the preceeding load applies your "having" condition to it.

Not applicable
Author

Hi John,

I am getting syntax error when using the Having clause in the Load statement. I searched in the help files also they had given only the Group Option and there is no Having clause.

johnw
Champion III
Champion III

You don't use a HAVING clause.

You write it like I showed it, with a PRECEEDING LOAD. For example:

LOAD *
WHERE Orders > 10 // This is your "having" clause.
;
LOAD Customer, count(Order) as Orders, sum(Sales) as Sales
RESIDENT OrderTable
WHERE OrderDate >= 20100101
GROUP BY Customer
;

Not applicable
Author

I am having trouble with this syntax as well. The issue I am experiencing is the Qlikview software tells me that the table cant be found. I am using a resident clause to reference the table. It is a salesforce.com table.....am I missing something?

johnw
Champion III
Champion III

"Resident" is used when you're loading from a table that you have already loaded into QlikView. It is not used for loading from some other data source. I don't know how a salesforce.com table works, but I'm guessing that you would use SQL to load from it. In that case, your having clause could go in the SQL itself, and there's no need for a preceeding load to duplicate its functionality. But I have no idea if I'm even answering the right question.

Not applicable
Author

I have a similar 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