Script Editor Features and Commands in QlikView

The QlikView Script Editor is the main workplace for developing the scripting elements of QlikView application. Some useful features in the Script Editor include certain menus. The menus described in this chapter are found in the menu bar at the top of the screen. Most commands can also be configured as buttons in the toolbars. The following post gives you an overview on the various menus:

Learn how to use QlikView, from beginner basics to advanced techniques, with online video tutorials taught by industry experts. Enroll for Free QlikView Training Demo!

The File menu

The File menu is a drop-down menu at the top of the screen, and contains the following commands. It contains commands regarding saving the script and document, reloading, importing scripts, hidden scripts, and access to the Table Viewer.

• Save Entire Document, Save Entire Document As… and Save External Script File: These commands are available from the File command menu and the Save Entire Document command is available as an icon in the toolbar. The commands save the corresponding files and allow you to save the document or script file alternatively as a named file. The Save External Script File command allows you to save the entire script as a .qvs file that can later be imported into other QlikView documents. ‘Save early and often’, the best practice is to save (Save Entire Document) before each reload after editing the script in a way. You can set up QlikView to automatically save the script before reloading the data in the User Preferences dialog box, accessed from the main QlikView screen by navigating to the Settings | User Preferences | Save menu command and checking the Save Before Reload checkbox. Let’s have a look at some of the commands:
• Reload: This is available from the File command menu or the toolbar icon. This command closes the Script Editor, reloads the script, and shows the progress of the script execution and any errors if encountered. If the script reload process is cancelled and the QlikView application already contains previously loaded data, QlikView will ask if you want to reload old data or not. The best practice is to accept the reload old data option, so sheet objects and charts display with data.
• Open External Script File: This command is a handy way to import previously saved script files (the .qvs files) and text (.txt) files containing scripts. When importing script files using this command, a newly imported script is added in a new script tab, with the name of the script file as the new tab name. This new script can be edited as needed once imported.
• Table Viewer: The Table Viewer is a fundamental tool for helping to create QlikView applications. The Table Viewer is also available (and easier to access) from both the icon toolbar in the Script Editor and the main icon toolbar in the main QlikView application. This tool allows you to graphically view the table structure, relationships, metadata, and associations of source and model tables in your application. The Table Viewer serves as the main troubleshooting and visualization tool for your data model.

Related Article: Snowflake and Star Schema in Qlikview

The Edit menu

The Edit menu contains basic script editing and tidying commands that make the script easier to read, or disable (via commenting) blocks of script code. Let’s have a look at the commands:

• Undo, Redo, Cut, Copy, Paste, Delete, Select All, Find/Replace, Indent, Outdent: These are the basic editing tools as found in many software packages such as Microsoft Word. Keyboard shortcuts for: Undo (Ctrl+Z) , Redo (Ctrl+Y), Cut (Ctrl+X), Copy (Ctrl+C), Paste (Ctrl+V).
• Clear Entire Script: This clears the active (open) script tab and removes the script tab (and cannot be undone). This command does not affect any other script tabs. You can also delete/ clear a script tab by using the Tab | Remove command.
• Upper Case, Lower Case, Invert Case, Capitalize: These commands control the manipulation of text case. These commands can be useful in code organization and changing the case of case-sensitive script areas (some areas of QlikView scripting are case sensitive, such as accounts and roles in Section Access, (which should be uppercase).
• Comment, Uncomment: These are very useful commands that allow you to add descriptive text comments (ignored by the script compiler) or disable portions of the script by using comments. You can also use other commenting commands, which will be discussed in the next section, Script commenting.

The Insert menu

The Insert menu contains powerful commands to insert many types of script elements into the script code, from variables to including files and connecting statements. Let’s have a look at the commands:

• Set Statement: This command allows you to create and define SET statements using custom variables or many predefined variables (system variables, error variables, and so on.).
• Environment Variables: This command inserts the standard list of environment variables that are usually present in a script when a new QlikView document is created (money, time, date, and so on). These statements can be edited once inserted into the script.
• Script Files: This command allows you to browse for a specific script files (.sql, .txt, or .qvs) and insert the contents of the script file at the cursor location.
• Include Statement: This command inserts a reference to a specific file (.sql, .txt, or .qvs). It differs from Insert | Script Files by not inserting the content directly into the QlikView script, but only referencing this file. The include file may contain a reusable script, query, connection string, or subroutine you wish to use in your script. The beauty of the include file command is that the data is reusable and does not have to be edited in QlikView itself. It is powerful when using connection strings, e-mail lists, and similar information that may change often.
• Domain SID: This command inserts the domain security ID into the script. This value is the identification number of the NT domain in which the user has logged in. This value may be used to set up section access when restricting users by NTDOMAINSID.
• Test Script: This command inserts a sample test script to illustrate the functioning of the QlikView script execution, objects, and debugging.
• Load Statement: This command allows for insertion of a load statement from either an external file (spreadsheet, text, XML, QVD, and so on) or an inline wizard that allows for the creation of data internally in a table.
• Section Access: This command inserts section access information either from the Publisher Authorization location published by the QlikView Management Console (usually in a format similar to HTTP:// localhost: 4780/ QMS/AuthTable ) or inline section access. If you are using Publisher Authorization, be sure to make the URL on the server a trusted site in Internet Explorer. If you are using inline section access, a wizard displays, allowing you to enter the necessary information and also desires the type of section access (username, NT domain, and so on).
• Connect Statement, Disconnect Statement: The Connect command opens the Connect to Data Source dialog, where the data source can be selected with the connection credentials. This will insert a connect string into the script. The Disconnect command will insert a disconnect command into the script, which will disconnect QlikView from the data source.

The Tab menu

As QlikView applications grow, the number of tabs that information is spread across can grow rapidly as well. The Tab menu is helpful in creating script tabs, moving the tabs to the left and right (promoting and demoting tabs) as well as renaming, merging, and removing tabs. Remember that tabs are a way of organizing and ordering script execution, and the script execution runs from the top of the leftmost tab to the bottom of the rightmost tab. Let’s have a look at the commands:

• Add Tab, Insert Tab at Cursor: These commands add a new script tab at the end of the tab set or at a place determined by the position of the cursor in the script, respectively.
• Rename: This command allows you to rename the active script tab.
• Promote, Demote: These commands move the tabs to the left or right, respectively. Tab locations control the execution of the script in that tab.
• Merge with Previous: This command merges the current active tab script with the previous (tab to the left) tab script information. It appends the current tab script information to the bottom of the previous tab script.
• Remove: This command removes the current active tab and also all the script information it contains.

Related Article: QlikView IntervalMatch Function

The Tools menu

The Tools menu assists with setting up the correct ODBC connection, setting the Script Editor preferences, and providing a quick syntax check feature. Let’s have a look at the commands:

• ODBC Administrator 64 bits, ODBC Administrator 32 bits: These commands open the correct ODBC Data Source Administrator dialog that allows you to set up the correct DSN information for the data source connection. Select the proper dialog based on your environment conditions and database.
• Editor Preferences: This command opens the User Preferences dialog that allows you to configure the Script Editor features, such as text font and size, help features, shortcuts, default scripting engine, and other style elements.
• Syntax Check: This command runs the QlikView syntax checker on the script code.

The Help menu

The Help menu displays help information for the specific section of QlikView which is active.

The Tools pane

The lower section of the Script Editor window is referred to as the Tools pane. This section has four tabs: Data, Functions, Variables, and Settings. The Tools pane of the Script Editor is where data sources are defined and connection strings are created, functions and values are created and edited, and other system settings are made.

• The Data tab: This tab allows you to create connections to databases and other data sources, connection strings, select statements, and universal file location settings (FTP or relative paths). Connections to ODBC and OLE DB data sources can be made from here as well as custom data sources if you have installed the proper .dll connector as required. A connector that serves as a way to create a QlikView Server administrative tool, QVSAdminDataProvider.dll, is also provided. This QVW metadata extractor can be used alongside, instead of, or together with other tools such as the QlikView Governance Dashboard, available for free from QlikMarket (market.QlikView.com).
• The Functions tab: From this tab, you can find a function type which is suitable for your application and paste it into the script. The Functions tab also contains the correct syntax to aid you in its use.
• The Variables tab: The custom variables you have created in the application can be found here and pasted into the script. You can also view and paste system variables into the script from this tab.
• The Settings tab: In the Settings tab, you can set script privileges to read and write from/to databases and execute external programs (turning these on decreases security and will cause an alert to be triggered, hence warning users). In this tab, you can also enable the scrambling of the connection credentials in the connect line of the script (inserted into the script when you click on the Connect button in the Data tab and select the data source and enter credentials).

Script commenting

Script commenting is a powerful way to organize, describe, and disable/enable lines or an entire section of your QlikView script. You can insert comments and remarks in the script code, or deactivate parts of the script code by using comment marks. For instance, it is QlikView’s best practice to include an explanatory comment before each Load script for a table. Adding comments to code is also necessary for later code review and editing by other QlikView developers. Often, code created one month ago will be difficult to understand during later months when being reviewed (even by the same developer!). Commented code will be marked by QlikView as green text. Following are some of the methods of script commenting available to you:

• Using rem (REM, Rem, or rem—it’s case insensitive) before a line
• Two forward slashes (//) before a line of code comments out that line in the script. All text on a line that follows to the right of // (two forward slashes) will be considered a comment and will not be executed when the script is run.
• One forward slash and asterisk before the line and one asterisk and forward slash after the lines (/*…< string >…*/) will comment out the entire enclosed code block
• Highlight the code you wish to mark as a comment or uncomment, right-click, and select either Comment or Uncomment from the shortcut menu.
• The data load editor toolbar contains a shortcut to commenting or uncommenting code. The function works as a toggle, if the selected code is not commented it will be commented, and vice versa.
• The highlighted text may be commented out and uncommented by choosing the Edit | Comment or Edit | Uncomment tabs, respectively, in the script editor command toolbar.
• Commenting

Related Article: QlikView Interview Questions

Do the following:

1. Select one or more lines of code that are not commented out, or place the cursor at the beginning of a line.
2. Click …, or press Ctrl+K.
The selected code is now commented out.

Uncommenting

Do the following:

1. Select one or more lines of code that are commented out, or place the cursor at the beginning of a commented line.
2. Click …, or press Ctrl+K.
The selected code is now not commented out.

Creating the Select statement

Assume that you have any database on your computer or server (Access, MySQL, Oracle, or otherwise— these illustrations use the free Microsoft Access 2010 database, Northwind1, on a system running the 32-bit Office 2010), assure that you have the correct driver on your machine; if you are running the 32 bit Microsoft Office 2010 and accessing the newer format of the Access .accdb file. You will need to download and install the 32-bit Microsoft Access Database Engine 2010 Redistributable driver. Let’s walk through creating a brief script in QlikView’s Script Editor window.

To get to the Select Statement dialog, you have to first connect to a database. The following example illustrates a connection to a Microsoft Access 2010 .accdb database file. Modify it as you need for your database or data source type.

1. Start by opening a new QlikView document (click on Cancel when the Select Data Source wizard appears). Go to the Script Editor (navigating to File | Edit Script from the main QlikView toolbar).
2. In the Data tab in the Tools pane of Script Editor, select OLE DB and check the Force 32 bit DB checkbox (we want QlikView to look for installed 32-bit connections).
3. Establish the connection to the database and insert the connect statement in the script by clicking on the Connect button on the Data tab in the Tools pane, and proceeding through the Data Link dialog.
4. Select Microsoft Office 12.0 Access Database Engine. On older systems or to access older Access .mdb databases, select the Microsoft Jet 4.0 OLE DB driver.
5. Click on the Connection tab and enter the full pathname and filename of the .accdb database. Also, enter any connection login credentials if required.
6. Test the data connection. If successful, click on OK, and then connect statements will be displayed in the script window.
7. Click the Select button in the Tools pane to proceed to the Create Select Statement dialog. The Create Select Statement dialog will be populated with the database tables and fields for your selection. If the connection was not successful, troubleshoot the connection credentials and path/ filename.

Building the Select statement

Once we’ve connected to the database, the Create Select Statement dialog appears and allows you to customize and narrow your selected statement by selecting various tables and files.

By default, the first table in the list is selected and all columns (select all or select star with an asterisk) are selected. Each Select statement can load information from one table and multiple columns (database fields).

In this example, we will select (Ctrl + click) from the Customers table using the following columns: Address, City, Company, Country/Region, E-mail address, First Name, Last Name, Mobile Phone, State/Province, and ZIP/ Postal Code. Leave checked the Preceding Load checkbox and click on OK.

The load /select statement is displayed in the script area in the script editor dialog. Note that QlikView syntax load statement appears above the SQL select statement, as shown in the following screenshot:

Running your script and viewing results

Click on Save and then on the Reload button. The Script Execution Progress dialog appears briefly and the Script Editor closes, with the main QlikView screen (sheet) open to the Sheet Properties dialog. This is the sequence you will see each time after reloading a script. It is fine to click on Cancel in the Sheet Properties dialog, unless you would like to add one or more fields to the existing sheet.

Click on Add All and then on OK to display all the available fields on the sheet. All fields are displayed as listboxes on the main QlikView sheet. Select the Layout | Rearrange Sheet Objects menu command to organize the list boxes. Make some selections and see how the data changes based on these selections. Click on the Clear button to reset the selections.

Organizing tabs in scripts

There are many preferences when choosing the script architecture, but the one most accepted, in the QlikView community, is dividing script tabs by data source and also creating additional tabs for the master calendar, variables, mapping tables, and also cleanup procedures. Remember that the scripts are executed from top to bottom and from left to right, so the tab scripts must be executed in the correct order.

Now that you’ve seen how to connect to a data source and load fields from database tables, you may be wondering about how to organize the tabs in the QlikView Script Editor window. It will help to put some thought into the script architecture up front: how will the tabs be organized— by function or by the application tab? How about by data source? How will you handle variables? Mapping tables?

It’s up to you, and it will probably be a hybrid approach. There are two main schools of thought in the QlikView community about tab organization: some say to organize them by data source and some say to organize them by QlikView application’s (QVW) user interface sheet tabs (for instance, each sheet tab will have a corresponding script tab).

The best way to organize and add tabs is likely the division along data source lines, with no consideration of the final user interface, QVW. Remember that the tab order is important and connections and selections must flow in a logical order (promote or demote tabs as needed and add new tabs using the Tab menu). You may want to add other tabs as well, such as mapping tables, variables, cleanup procedures, and exit script.

Whatever you do, make your tab names and tab order very descriptive and logical, and add plentiful comments to describe various parts of the script for easier maintenance later on.