The Create Select Statement dialog is opened by clicking the Select button in the Data page of the tool pane in the Edit Script dialog. It is used to define the tables and fields to be loaded from a previously selected data source.
The dialog consists of three groups. The first group contains information about the data source.
| Data Source | Name of the current ODBC/OLE DB data source. |
| Database | It is possible to choose between available databases via the drop-down list. |
| Owner | The database owner is shown here. It is possible to choose between available owners via the drop-down list. |
| Connect | By clicking this button, the Connect to Data Source dialog opens. |
| Driver | Click this button to open the ODBC Driver Information or OLEDB Driver Information dialog, containing information about supported functionality in the driver currently used. |
| Support | Opens a dialog with support information about the database. The information can be used whenever contacting Qlik support and when reporting bugs or problems in relation to QlikView documents. |
The second group is used for selecting tables and fields.
| Database Tables | This list shows available database tables. Click on a table name to select it. The types of tables that appear in the list can be controlled via the check boxes to the left (see description below). |
| Fields | This list shows all available fields in the selected table. Choose one or more fields from the list to be included in the select statement. Use " * " in order to select all of the field names at once. The selection of fields will be reflected in the Script page of the third group (see description below). Fields can be sorted in Original Order from the database or in alphabetical Text Order by selection in the drop-down control. |
| Show Icon for Key Fields | When this alternative is checked, any field that is defined as a key field in the source database will be shown with a key icon in the Fields list. |
| Tables | This check box must be selected if regular database tables are to appear in the Database Tables list. |
| Views | This check box must be selected if database views are to appear in the Database Tables list. |
| Synonyms | This check box must be selected if database table synonyms are to appear in the Database Tables list. |
| System Tables | This check box must be selected if database system tables are to appear in the Database Tables list. |
| Aliases | This check box must be selected if database table aliases are to appear in the Database Tables list. |
The third group shows the generated select statement and information about the selected table and fields. The group contains the following pages:
Script
The Script tab shows the script about to be generated for the select statement.
The three radio buttons to the right control the formatting of the select statement in the script.
| Column | Select this option to generate the select statement with each field name appearing on a separate row in the script. |
| Row | Select this option to generate the entire select statement appearing on a single row in the script. |
| Structured | Select this option to generate the select statement on structured rows in the script. |
| Preceding Load | If this option is marked, the select statement will be generated with a preceding load statement using the select statement as input. All fields will be listed in the load statement, even if * is used in the select statement. |
| Add | Click this button to save the select statement generated so far. The generation of a new select statement is initiated without leaving the dialog. The old statement will be visible in the preview pane above a clear divider. This procedure may be repeated any number of times. |
Table
The Table tab shows detailed information about the selected database table.
More... opens a separate sizable dialog with an expanded view of the Table, Columns and Preview pages.
Columns
The Columns tab shows detailed information about the selected columns (fields) of the current table.
Preview
The Preview tab shows a preview of the first rows that will be generated by the current select statement.
Blobs
The Blobs tab makes it possible to bundle so-called Blobs (Binary Large Objects), in a QlikView document. It is usually a picture, a text file or similar, stored as a single entity in a database management system. The i icon indicates that the field contains a blob. The blobs in the database are listed together with size (in KB) and type in the Blob Viewer. The Blob is previewed next to the list if it is an image. QlikView supports jpg, png, bmp, pdf and rtf blobs.
The Bundle... button opens the Bundle dialog.
| Key Field for the Blob data | In the list select the blob that should be resized. |
| Shrink Images | Enable the option to resize the blob. Apply Pixel Width and Pixel Height to resize the image to fit. |
The setting does not handle aspect ratio.
Once the selections of table and fields has been made it is possible to click OK to insert the select statement(s) generated at the cursor position in the QlikView script. Pressing Cancel will abandon changes.
The ODBC driver can usually interpret far more complicated select statements than the ones which can be produced by this dialog. An alternative way to generate a more complex select statement is to use a query tool, e.g. Microsoft Query, and graphically make the select statement there. When this is done, copy (Ctrl+C) the select statement and paste it (Ctrl+V) into the QlikView script. (In MS Query, click the SQL button.)
