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.

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

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.

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: