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.

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.

DirectDistinctSupport

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.

Did this information help you?

Thanks for letting us know. Is there anything you'd like to tell us about this topic?

Can you tell us why it did not help you and how we can improve it?