Custom SQL examples for Direct Query
The following examples show some of the options when using custom SQL for Direct Query.
Example: Adding custom SQL definitions to the Direct Query model
In this example, custom SQL definitions for tables C, R, N are added to the data model in the Direct Query application along with two inter-table relationships. The use of the Coalesce function is used to check if a field value of N_NATIONKEY is equal to the value of C_NATIONKEY, or if both are equal to null.
Coalesce is the recommended way to include a check to see if a field value is equal to null. For more information, see coalesce Script and chart function.
Example: Adding tables with custom SQL to a data model made with Data model manager
In this example, the Direct Query data model is first constructed with the contents in the DirectQueryModel.main object, which was created with Data model manager. Then an additional table, C with the fields C_CUSTKEY and C_ACCTBAL, are added to the data model. A relationship is made between the field O_CUSTKEY in ORDERS with the field C_CUSTKEY in C.
Example: Constructing a Direct Query data model with a custom SQL table
In this example, the Direct Query data model is constructed with a custom SQL table, C, with the renamed fields C_KEY and C_BAL. Next, IMPORT LIVE adds the data model created in Data model manager from DirectQueryModel.main. This data model contains the table Orders and has an existing relationship between Orders and Customer in that model. Then, the existing relationship between Customers and Orders is dropped and a new relationship is defined between Orders and C.
Example: Constructing a data model including variables
In this example, the variable MULT is defined first with a value of 100. Then, it is applied as a multiplier to the sum of PS_Supplycost to calculate the field S.
Example: Constructing a data model with Qlik expressions
In this example, both variables and expressions are used to build the data model. In the table ORDERS_AGGREGATIONS, expressions are used to define the values in ORDERS_GROUP_TOTAL_PRICE. Whenever selections are made in a sheet that would impact the values in ORDERS_GROUP_TOTAL_PRICE, the expression will be re-evaluated on the data returned by the query to the database.
Example: Using functions in custom SQL expressions
The following custom SQL statement demonstrates a simple use of a $(=…) Engine expression that is expanded when executed in the custom SQL. This expression retrieves remote database rows depending on the application session's user information. When a chart, query, or selection is performed by a Direct Query application session user with email address 'user1@qlik.com' then the dynamic retrieval of data rows from the remote SQL database is filtered to match only the rows where the C_NAME field contains the value 'Customer#000000007'. Otherwise, if the Direct Query application's session user's email is not 'user1@qlik.com', only the rows where the C_NAME field contains the value 'Customer#000000009' will be retrieved and shown.