Creating a Direct Query app with custom SQL

Create data models in the load script with a custom SQL query in your Direct Query app. Custom SQL offers an alternative to using Data model manager to define your data model for Direct Query.

Using custom SQL to create Direct Query data models allows you to use SQL statements to define tables and fields that are generated by the SQL query itself. In addition, custom SQL statements in a Qlik app can use load script variables and Qlik expression macros in your custom SQL statements. This allows for a more dynamic data model and enables your custom SQL-defined tables and columns to adjust to variable or expression evaluation results.

Custom SQL can be used on its own in your load script. You can also use a combination of Data model manager and custom SQL in Data load editor to define your data model.

To create data models with custom SQL:

1. Add section directquery to your load script.

   Only one SECTION DIRECTQUERY section can exist in the load script. The section must be the last section in a load script.

   Even if you have not defined a data model with Data model manager, adding a LIB CONNECT statement and then a section directquery statement to your load script and loading the script will turn your app into a Direct Query app.

2. Build your data model with the following statements:

   • SELECT

     Use SELECT statements to select the tables and columns to load. For example: <TableName> SELECT <custom SQL>.

     If you do not define your own table names in the load script, tables will be named Table<script line number>.

     Select

   • CREATE RELATIONSHIP

     Use CREATE RELATIONSHIP to define relationships between fields and tables in the data model.

     Create relationship

   • DROP RELATIONSHIP

     Use DROP RELATIONSHIP to drop previously defined relationships from the data model.

     Drop relationship

   • DROP TABLE

     Use DROP TABLE to drop previously defined tables from the data model to avoid possible conflicts between tables.

   • LET, SET

     Use LET and SET statements to define variables or Qlik expressions.

     Let

     Set

   Tables and fields must be uniquely named across your data model. If you define a table in the Data model manager and then try to redefine the table in custom SQL, it will fail unless you either drop the table created in Data model manager or rename the table and fields in your custom SQL.

Any script statements not supported within the section directquery section generates an error when loading data. Any settings for script debugging or forced continue are ignored.

For examples of custom SQL load scripts, see Custom SQL examples for Direct Query.

Custom SQL, variables, and expressions

Custom SQL definitions in Direct Query allow you to use both script variables and Qlik expression macros within a custom SQL statement.

This ability brings a dynamic variable and expression influence and flexibility to an app's data model with custom SQL-defined tables and columns adjusting to variable or Qlik expression evaluation results.

Variables in custom SQL

Use SET or LET statements to define your variables. Add variables in your load script using the format $(<variable name>).

Example: Constructing a data model including variables

Variable references in custom SQL are evaluated at reload time. If the variable value changes during use of the app, the change will not be reflected in the custom SQL using that variable unless a script reload is performed.

If you want dynamic variables in custom SQL definitions, reference variables as parts of Qlik expressions to force those variables to be evaluated on every query execution that uses those custom SQL definitions.

For more information on variables, see Working with variables in the data load editor

Expressions in custom SQL

You can use expressions to modify the content of custom SQL being sent to the remote database to represent tables and columns in the app data model. Expressions used in the data model must resolve to a single value.

Expressions are evaluated dynamically on every query execution that uses those SQL definitions. Any time a custom SQL statement of tables or fields uses Qlik expressions during execution of a query, those Qlik expressions are evaluated first to retrieve the current results of the expressions. These results inserted into the custom SQL statement to be executed on behalf of the original query. It is possible that these evaluations of Qlik expressions may in turn generate SQL queries out to the remote database. It is possible that these evaluations of Qlik expressions may in turn generate SQL queries out to the remote database.

If an expression fails to expand during load, it will return a null value. This does not mean your expression is invalid. Your expression may require selections from a user to be made in an app to evaluate.

Example: Constructing a data model with Qlik expressions

Creating a data model with custom SQL and Data model manager

You can use a combination of custom SQL and Data model manager to create your data model for Direct Query. There are some practical considerations, however:

• As a best practice, complete any modeling you want to do in Data model manager before adding custom SQL to the load script. custom SQL and Data model manager are best used together when Data model manager is used to define the loaded tables and relationships between fields, and custom SQL is used for additional customizations.

• The portion of the data model created byData model manager and the portion created by custom SQL are managed separately and then combined together during the script reload. Data model manager does not display any of the data model defined by custom SQL. To view your view your full data model, use Data model viewer.

• The data model created by Data model manager and the data model defined by your custom SQL must use the same database connection.

• You can move the IMPORT LIVE statement added to your load script by Data model manager. The load script is evaluated sequentially, so its new position may impact your custom SQL requirements. For example, it could create unwanted relationships or duplicate table names in the final data model.

• Tables and fields must be uniquely named across your data model. If you define a table in the Data model manager and then try to redefine the table in custom SQL, it will fail unless you either drop the table created in Data model manager or rename the table and fields in your custom SQL.

Limitations

Custom SQL for Direct Query has the following limitations:

• Custom SQL can only use a single database.

• A Direct Query app using one or more custom SQL statements with embedded Qlik expression macros cannot be used as a selection app for On-Demand apps. This is because any generated template app, being an in-memory app, cannot support Qlik expression macros in its load script.

• Direct Query custom SQL are not supported for scripts added in the Analytics activity center. For more information on scripts, see Working with scripts in the catalog.