Direct Query
The DIRECT QUERY statement allows you to access tables through an
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
Native data-source syntax can be used when the user knows the database to be queried and wants to exploit database-specific extensions to
- 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 |
---|---|
|
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. |
|
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. |
|
The full syntax of database WHERE clauses is not defined here, but most 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
For example, the SELECT statement:
SalesOrderID,
RevisionNumber,
OrderDate,
SubTotal,
TaxAmt
FROM MyDB.Sales.SalesOrderHeader;
could be changed to the following DIRECT QUERY statement:
DIMENSION
SalesOrderID,
RevisionNumber
MEASURE
SubTotal,
TaxAmt
DETAIL
OrderDate
FROM MyDB.Sales.SalesOrderHeader;