Skip to main content

On this page

Using subqueries with Direct Discovery

If the cardinality of the key field joining the table is high, that is, it contains a large number of distinct values, a selection in Qlik Sense may generate a very large SQL statement as the WHERE key_field IN clause can contain a large number of values. In this case, a possible solution is to let Qlik Sense create subqueries instead.

To illustrate this, we use an example where a products table (ProductTable) is linked to a sales order table (SalesOrderDetail) using a product id (ProductID), with both tables used in Direct Discovery mode.

Two linked tables.

We create a chart with OrderMonth as dimension, and Sum(Subtotal) as measure, and a filter box for selecting Size.

A filter box, a bar chart and a table.

Scenario 1: Low cardinality

In this scenario, the product table contains a low number of distinct products, 266. If we make a selection in Size, Direct Discovery generates a SQL statement to return the data, using a WHERE ProductID IN clause containing the product IDs matching the selected size, in this case 19 products.

A filter box with selections made, a bar chart and a table.

The SQL that is generated looks like this:

SELECT ProductID, month([OrderDate]), SUM(OrderQty), SUM(SubTotal)
FROM SalesTable
WHERE ProductID IN ( 12, 51, 67, 81, 89, 92, 100, 108, 142, 150, 151, 162, 191, 206, 220, 222, 251, 254)
GROUP BY ProductID, month([OrderDate])

Scenario 2: Using subqueries

If the same example contains a high number of distinct products, for example 20.000, selecting a dimension filter, Size for example, would generate a SQL statement with a WHERE ProductID IN clause containing thousands of product IDs. The resulting statement could be too large to be handled by the data source due to limitations or issues with memory or performance.

The solution is to let Qlik Sense create subqueries instead, by setting the DirectEnableSubquery to true. The generated SQL statement could look like this instead:

SELECT ProductID, month([OrderDate]), SUM(OrderQty), SUM(SubTotal)
FROM SalesTable
WHERE ProductID IN
( SELECT DISTINCT "AW2012"."dbo"."PRODUCT"."PRODUCTID" WHERE "AW2012"."dbo"."PRODUCT"."SIZE" IN (3))
GROUP BY ProductID, month([OrderDate])
 

The WHERE ProductID IN clause size is not dependent on the number of keys resulting from the selection anymore.

The following limitations apply when using subqueries:

  • Subquery syntax is only invoked when you select data which involves filtering a chart using data from another table.
  • The amount of data within the keys is the determining factor, not the number of keys.
  • Subqueries are only invoked if all tables involved are in Direct Discovery mode. If you filter the chart using data from a table included in memory mode, an IN clause will be generated.