Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Is there any function similar to Having Clause of Sql Statement.
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
;
No, unfortunately not, you have to make a workaround, for example by creating a temporary table.
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
;
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.
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.
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.
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
;
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?
"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.
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