In this tutorial we will learn about how to create a QlickView Script.
The script is a code that controls data connections, extractions, modification, association, and storage. This chapter will give you the basics of building a QlikView script and corresponding data model, including load statements, script types, Script Editor commands and basic script organization.
The QlikView script is a block of code written in a special SQL-like QlikView syntax language, which controls the various parts of the data extraction, transformations, and loading (storage) of data from databases and files to the QlikView application or of data exported to the QlikView storage files (QVDs). The script is created and edited in the Edit Script dialog in QlikView and is usually arranged in tabular format for organization purposes. There are many commands forming the QlikView syntax and rules for forming scripts. In this chapter, we will glance at some basic elements of data modeling and script building.
A data model is a logical (or conceptual) depiction of how data is related and queried in a system. In a traditional relational database management system (RDBMS), entity relationship data models reign supreme: individual database tables are created to maximize the efficiency when entering, editing, and storing data. Data is typically not repeated in a relational database, and this eases data input and edits— but makes it much more difficult to retrieve data (query) from the database. Retrieving data from a relational database requires the user or developer to have knowledge of the data structure (in tables where the data resides).
Dimensional modeling (or associative modeling in QlikView-speak) is a more efficient way to arrange data tables that results in a more natural means of querying data. Dimensional modeling in QlikView is the creation of a new data structure (data model) from existing databases and files containing data you want to query. QlikTech, the makers of QlikView, likes to say that this method of associative data modeling works in the way the human brain works— forming relationships between data and allowing questions to be asked starting from any point in the system. This method of modeling is the output of the QlikView script, and it is essential that you take the time to sketch out the data model prior to starting scripting. Preparation is the key to any QlikView project, and time invested in designing your data model will pay off in reducing troubleshooting and testing time later on.
QlikView forms the associative data model by automatically joining the tables that have identical field names and the same case. This can cause problems by inadvertent joins on fields that have no relationship, but this can be remedied through reassigning names to fields (aliasing or mapping) or concatenating information to combine fields into existing tables.
Related Article: QlikView Interview Questions
Accessing the Script Editor
Scripting is a very powerful feature in QlikView, which enables control of the data load options and data transformations. It enables the use of many inbuilt functions available in QlikView and creates subroutines to be used across multiple scripts within a QlikView document. Scripting is done using the Script Editor. It is accessed from the File menu using Script Editor (also called as the Edit Script dialog).
It is the main work area in QlikView, and undoubtedly the most important part of the QlikView application. This window (or dialog) can be accessed from an open QlikView document either by clicking the Edit Script icon, navigating to the File | Edit Script command in the QlikView command bar, or by holding the Ctrl + E keyboard shortcut.
When opening a new QlikView document, the Create Script wizard will display, prompting you through a step-by-step method (Select Data Source) to extract data from an Excel document. Usually, you will want to bypass the Create Script wizard. To do so, click on Cancel, then select the Edit Script icon, File | Edit Script command, or Ctrl + E to open the Script Editor window.
The Script Editor window is displayed. The main part of the Script Editor is the large scripting pane, where scripts are created and edited. By default, the script is already populated with common settings that describe how QlikView should handle numeric values, currency, time, date, timestamp, month, and days.
The Hidden Script feature in QlikView is useful when you want to hide portions or all of your QlikView script from users. This feature is typically used to hide the Section Access portion of a load script. Hidden script in QlikView is needed to hide section access involving user credentials from being displayed during the reload process. But, hidden script password in Qlikview is not recoverable.
Hidden scripts can be created from the Script Editor dialog by selecting the File | Create Hidden Script menu command. The New Hidden Script Password dialog box appears, prompting you to enter a password for the hidden script and confirm the password. When you click OK, a new script tab will appear with a tiny key icon on it, indicating a password-protected hidden script. To add new tabs to the hidden script portion of your script, select the hidden script tab and click on the New Tab icon. This will create additional hidden script tabs. If you want to create non-hidden script tabs, select an unprotected script tab and click on the New Tab icon.
When you have the hidden script tab displayed (you’ve already entered the hidden script password during the QlikView session), you can also manage the hidden script using a couple more commands: navigating to File | Remove Hidden Script will remove the hidden script entirely from your QlikView script, and navigating to File | Change Hidden Script Password allows you to enter and confirm a new hidden script password.
Once you have created and saved the QlikView document with the hidden script, you can edit the main script as usual (selecting File | Edit Script from the main QlikView application), but the hidden script portion of the script is not displayed until the File | Edit Hidden Script command is selected from the Script Editor window.
Adding a hidden script can be a good idea when you want to protect the editing or viewing of section access or to protect intellectual property, but it comes with some downsides and caveats:
Binary loading will not be allowed in any applications with a hidden script.
The hidden script is always at the leftmost side of the scripting dialog and cannot be moved. The scripts always run in tab order, so the hidden script (and all hidden script tabs) will be executed before the main (unprotected) script runs.
The progress information displayed in the script execution window will not be displayed when the hidden script is running.
If the log information is enabled for the script, no logging will be recorded if a hidden script exists.
Session access in the main script will not be allowed if section access already exists in the hidden script