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 QlikView may generate a very large
To illustrate this, we use an example where a products table (
We create a chart with
Scenario 1: Low cardinality
In this scenario, the product table contains a low number of distinct products, 266. If we make a selection in
Scenario 2: Using subqueries
If the same example contains a high number of distinct products, for example 20.000, selecting a dimension filter,
The solution is to let QlikView create subqueries instead, by setting the DirectEnableSubquery to true. The generated
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.