For those of you, who come from ANSI SQL background know what is the HAVING clause; however, some of the QlikView developers who doesn’t have ANSI SQL background might not understand it. Before we understand what is the HAVING clause let’s first understand what is the WHERE clause.
What is the WHERE clause?
Using the WHERE clause, we specify the predicate logic to filter the rows from the QlikView LOAD script statements. You can use the WHERE clause in both LOAD & SELECT script statements. Logically, QlikView evaluates the condition to TRUE/FALSE. For each row where the condition is evaluated as FALSE, QlikView will filter out the rows.
Example of the WHERE clause:
1) Below QlikView script will LOAD the data from MyTable and it will evaluate the condition on MyID which checks for the value “100” and rest of the rows are ignored from MyTable
WHERE MyID = 100;
2) Below QlikView script will LOAD the data from MyTable and it will evaluate the condition on MyID which checks if the value is “>= 100 AND <= 200” and rest of the rows are ignored from MyTable
WHERE MyID >= 100 AND <= 200;
What is the HAVING clause?
What the WHERE clause is for individual rows; same way, the HAVING clause is for GROUP BY clause. The HAVING always works on the GROUP BY clause. However, we don’t have the HAVING clause in QlikView. But we can use the PRECEDING LOAD with the WHERE clause on the normal LOAD/SELECT statements which has same effect as the HAVING clause.
Example of the HAVING clause:
//Preceding Load statement with the WHERE clause which works as the HAVING clause on the aggregated group
WHERE AvgYearlyIncome > 50000;
//Normal Load statement with aggregation and GROUP BY clause
Avg (YearlyIncome) AS AvgYearlyIncome
GROUP BY Education;
QlikView starts code execution from left to right and top to bottom. However, whilst using the PRECEDING LOAD statement first the immediate following script (script in blue double braces) is executed then the preceding/outer script (script in green double braces) is executed. We can take advantage of this functionality to create the non-existent HAVING clause in QlikView.