The DIRECT QUERY statement allows you to access tables through an ODBC or OLE DB connection using the Direct Discovery function.
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.
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:
DIMENSION Dim1, Dim2MEASURENATIVE ('X % Y') AS X_MOD_Y
DIMENSION Dim1, Dim2MEASURE X, YFROM TableNameWHERE NATIVE ('EMAIL MATCHES "\*.EDU"')
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. |
| 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.
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).
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;