Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

having clause in Qlikview

Hi,

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

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

having clause in Qlikview

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
Valued Contributor II

SV:having clause in Qlikview

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

MVP
MVP

having clause in Qlikview

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

Not applicable

having clause in Qlikview

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.

MVP
MVP

having clause in Qlikview

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

having clause in Qlikview

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.

MVP
MVP

having clause in Qlikview

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

having clause in Qlikview

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?

MVP
MVP

having clause in Qlikview

"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

Re: having clause in Qlikview

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