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".

DIRECT QUERY Dimension STATEID as MyState Measure AMOUNT from SALES_TABLE;

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.

SQL Select STATEID as MyState, STATENAME from STATE_TABLE;

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

Load STATEID as MyState, STATENAME;
SQL Select STATEID, STATEMENT from STATE_TABLE;

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 QUERY

DIMENSION

SalesOrderID, RevisionNumber

MEASURE

SubTotal AS "Sub Total"

FROM AdventureWorks.Sales.SalesOrderHeader;

DIRECT QUERY

DIMENSION

"SalesOrderID" AS "Sales Order ID"

MEASURE

SubTotal,TaxAmt,(SubTotal-TaxAmt) AS "Net Total"

FROM AdventureWorks.Sales.SalesOrderHeader;

DIRECT QUERY

DIMENSION

(2*Radius*3.14159) AS Circumference,

Molecules/6.02e23 AS Moles

MEASURE

Num1 AS numA

FROM TableName;

DIRECT QUERY

DIMENSION

concat(region, 'code') AS region_code

MEASURE

Num1 AS NumA

FROM TableName;

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:

DIRECT QUERY DIMENSION stateid, SUM(amount*7) AS MultiFirst MEASURE amount FROM sales_table;

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:

month(ModifiedDate) as Mth

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!

Leave your feedback here