Direct Query

The DIRECT QUERY statement allows you to access tables through an ODBC or OLE DB connection using the Direct Discovery function.

Note: This functionality is not available in Kubernetes.

Syntax:  

DIRECT QUERY DIMENSION fieldlist [MEASURE fieldlist] [DETAIL fieldlist] FROM tablelist

[WHERE where_clause]

 

The DIMENSION, MEASURE, and DETAIL keywords can be used in any order.

The DIMENSION and FROM keyword clauses are required on all DIRECT QUERY statements. The FROM keyword must appear after the DIMENSION keyword.

The fields specified directly after the DIMENSION keyword are loaded in memory and can be used to create associations between in-memory and Direct Discovery data.

Note: The DIRECT QUERY statement cannot contain DISTINCT or GROUP BY clauses.

Using the MEASURE keyword you can define fields that Qlik Sense is aware of on a “meta level”. The actual data of a measure field resides only in the database during the data load process, and is retrieved on an ad hoc basis driven by the chart expressions that are used in a visualization.

Typically, fields with discrete values that will be used as dimensions should be loaded with the DIMENSION keyword, whereas numbers that will be used in aggregations only should be selected with the MEASURE keyword.

DETAIL fields provide information or details, like comment fields, that a user may want to display in a drill-to-details table box. DETAIL fields cannot be used in chart expressions.

By design, the DIRECT QUERY statement is data-source neutral for data sources that support SQL. For that reason, the same DIRECT QUERY statement can be used for different SQL databases without change. Direct Discovery generates database-appropriate queries as needed.

Native data-source syntax can be used when the user knows the database to be queried and wants to exploit database-specific extensions to SQL. Native data-source syntax is supported:

  • As field expressions in DIMENSION and MEASURE clauses
  • As the content of the WHERE clause

Examples:

DIRECT QUERY
DIMENSION Dim1, Dim2
MEASURE
NATIVE ('X % Y') AS X_MOD_Y
FROM TableName
DIRECT QUERY
DIMENSION Dim1, Dim2
MEASURE X, Y
FROM TableName
WHERE NATIVE ('EMAIL MATCHES "\*.EDU"')
Note: The following terms are used as keywords and so cannot be used as column or field names without being quoted: and, as, detach, detail, dimension, distinct, from, in, is, like, measure, native, not, or, where

Arguments:  

Argument Description
fieldlist

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 Qlik Sense 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.

Direct Discovery field lists

tablelist

A list of the names of tables or views in the database from which data will be loaded. Typically, it will be views that contain a JOIN performed on the database.
where_clause

The full syntax of database WHERE clauses is not defined here, but most SQL "relational expressions" are allowed, including the use of function calls, the LIKE operator for strings, IS NULL and IS NOT NULL, and IN. BETWEEN is not included.

NOT is a unary operator, as opposed to a modifier on certain keywords.

Examples:

WHERE x > 100 AND "Region Code" IN ('south', 'west')
WHERE Code IS NOT NULL and Code LIKE '%prospect'
WHERE NOT X in (1,2,3)

The last example can not be written as:

WHERE X NOT in (1,2,3)

Example:  

In this example, a database table called TableName, containing fields Dim1, Dim2, Num1, Num2 and Num3, is used.Dim1 and Dim2 will be loaded into the Qlik Sense dataset.

DIRECT QUERY DIMENSTION Dim1, Dim2 MEASURE Num1, Num2, Num3 FROM TableName ;
 

Dim1 and Dim2 will be available for use as dimensions. Num1, Num2 and Num3 will be available for aggregations. Dim1 and Dim2 are also available for aggregations. The type of aggregations for which Dim1 and Dim2 can be used depends on their data types. For example, in many cases DIMENSION fields contain string data such as names or account numbers.Those fields cannot be summed, but they can be counted: count(Dim1).

Note: DIRECT QUERY statements are written directly in the script editor. To simplify construction of DIRECT QUERY statements, you can generate a SELECT statement from a data connection, and then edit the generated script to change it into a DIRECT QUERY statement.
For example, the SELECT statement:

SQL SELECT
SalesOrderID,
RevisionNumber,
OrderDate,
SubTotal,
TaxAmt
FROM MyDB.Sales.SalesOrderHeader;

could be changed to the following DIRECT QUERY statement:

DIRECT QUERY
DIMENSION
SalesOrderID,
RevisionNumber

MEASURE
SubTotal,
TaxAmt

DETAIL
OrderDate

FROM MyDB.Sales.SalesOrderHeader;

See also: