Direct Discovery field lists

A field list is a comma-separated list of field specifications, fieldname {, fieldname}. A field specification can be a field name, in which case the same name is used for the database column name and the field name. Or a field specification can be a field alias, in which case a database expression or column name is given a Qlik Sense field name.

Field names can be either simple names or quoted names. A simple name begins with an alphabetic Unicode character and is followed by any combination of alphabetic or numeric characters or underscores. Quoted names begin with a double quotation mark and contain any sequence of characters. If a quoted name contains double quotation marks, those quotation marks are represented using two adjacent double quotation marks.

Qlik Sense field names are case-sensitive. Database field names may or may not be case-sensitive, depending on the database. A Direct Discovery query preserves the case of all field identifiers and aliases. In the following example, the alias "MyState" is used internally to store the data from the database column "STATEID".

This differs from the result of an SQL Select statement with an alias. If the alias is not explicitly quoted, the result contains the default case of column returned by the target database. In the following example, the SQL Select statement to an Oracle database creates "MYSTATE," with all upper case letters, as the internal Qlik Sense alias even though the alias is specified as mixed case. The SQL Select statement uses the column name returned by the database, which in the case of Oracle is all upper case.

To avoid this behavior, use the LOAD statement to specify the alias.

In this example, the "STATEID" column is stored internally byQlik Sense as "MyState".

Most database scalar expressions are allowed as field specifications. Function calls can also be used in field specifications. Expressions can contain constants that are boolean, numeric, or strings contained in single quotation marks (embedded single quotation marks are represented by adjacent single quotation marks).

Examples:  

Direct Discovery does not support using aggregations in LOAD statements. If aggregations are used, the results are unpredictable. A LOAD statement such as the following should not be used:

The SUM should not be in the LOAD statement.

Direct Discovery also does not support Qlik Sense functions in Direct Query statements. For example, the following specification for a DIMENSION field results in a failure when the "Mth" field is used as a dimension in a visualization: