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

QlikView 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 QlikView 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 byQlikView 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 QlikView 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 chart:

month(ModifiedDate) as Mth

Did this information help you?

Thanks for letting us know. Is there anything you'd like to tell us about this topic?

Can you tell us why it did not help you and how we can improve it?