topic having clause in Qlikview in QlikView

having clause in Qlikview

Fri, 07 Jan 2011 08:07:03 GMT

Hi,

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

SV:having clause in Qlikview

gandalfgray — Fri, 07 Jan 2011 10:14:25 GMT

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

having clause in Qlikview

johnw — Sat, 08 Jan 2011 02:56:14 GMT

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
;

having clause in Qlikview

Mon, 10 Jan 2011 05:50:26 GMT

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.

having clause in Qlikview

johnw — Mon, 10 Jan 2011 19:01:20 GMT

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.

having clause in Qlikview

Tue, 11 Jan 2011 08:08:20 GMT

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.

having clause in Qlikview

johnw — Tue, 11 Jan 2011 18:36:16 GMT

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
;

having clause in Qlikview

Thu, 03 Feb 2011 13:46:11 GMT

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?

having clause in Qlikview

johnw — Thu, 03 Feb 2011 21:45:37 GMT

"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.

Re: having clause in Qlikview

Fri, 04 May 2012 08:00:10 GMT

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

Re: having clause in Qlikview

Tue, 30 Jul 2013 11:59:01 GMT

Hello

Can you help me to adapt this SQL for QlikView?

SELECT FULLNAME_RAW_SOURCE || ' ' || BIRTHDATE AS FIO_DR, COUNT (*)

FROM tab1

GROUP BY FULLNAME_RAW_SOURCE || ' ' || BIRTHDATE

HAVING COUNT (FULLNAME_RAW_SOURCE || ' ' || BIRTHDATE) > 1

I need a unique record "name + date of birth" in the case, if it more than one in the source table (tab1).

Re: having clause in Qlikview

Tue, 01 Jul 2014 21:10:15 GMT

Hello, try this article

HAVING Clause In QlikView