Skip to main content

How To Create List Boxes and Table Boxes with Direct Discovery

List Boxes

List Boxes can be created with DIMENSION fields and MEASURE fields. When using a MEASURE field in a List Box expression, the aggr() function should be used to show the aggregated value of the MEASURE field with a DIMENSION field. Selections made with DIMENSION fields in a List Box generate SQL queries on the data source to create the associative table in memory.

DETAIL fields are not available in List Boxes.

Information note

Because List Boxes display only the unique values of a DIMENSION field, the query QlikView generates to display List Box data uses the DISTINCT keyword to retrieve data from the database. However, some databases require that the query instead include a GROUP BY class instead of the DISTINCT keyword. If the Direct Discovery table comes from a database that requires GROUP BY, you use the DirectDistinctSupport variable to change the default behavior of the DIMENSION queries.


Table Boxes

Table Boxes can display all Direct Discovery field types and provide a means to drill to the details of records that contain Direct Discovery fields. Table Boxes are the only QlikView objects in which fields designated DETAIL in the Direct Query load statement can be used.

When Direct Discovery fields are used in a Table Box, a threshold is set to limit the number of rows displayed. The default threshold is 1000 records. The default threshold setting can be changed by setting the DirectTableBoxListThreshold variable in the load script. For example:

SET DirectTableBoxListThreshold=5000

The threshold setting applies only to Table Boxes that contain Direct Discovery fields. Table Boxes that contain only in-memory fields are not limited by the DirectTableBoxListThreshold setting.

No fields are displayed in the Table Box until the selection has fewer records than the threshold limit.

Direct Discovery fields can be mixed with in-memory fields in a Table Box. Table Boxes that contain only Direct Discovery fields must include a DIMENSION field.

Table Boxes with Direct Discovery data show all selected rows even if they contain identical data. Table Boxes with in-memory data, on the other hand, shows only one of the selected rows when they contain identical data.

Join the Analytics Modernization Program

Remove banner from view

Modernize without compromising your valuable QlikView apps with the Analytics Modernization Program. Click here for more information or reach out: