In the previous chapter, we looked at building the frontend objects for use in dashboards, analyses, and reports. While it may be tempting to keep working on the frontend, we still have a bit of work to do on the backend. Very often input data is not in the exact same format as is required in the target data model, so in this chapter we will look at how we can use QlikView's built-in scripting language to transform our data.
We will first look at the script editor and some of the most important script statements, and see how we can use them to manipulate tables and control the flow of the script. This is followed by a look at some of the most commonly used functions for dealing with conditions and various data types. As QlikView scripts can get quite big and complex we will look at some ways in which we can debug our scripts. Next, we look at how we can properly organize and standardize our scripts so that they are easy to understand and maintain. We conclude this chapter by looking at how we can re-use parts of our script within and between our QlikView documents.
In this chapter, specifically, we will learn about:
- The Script Editor
- The most important script statements and how to use them to manipulate tables and control the flow of the script
- Operators and functions for dealing with various data types
- The options for debugging your script
- How to organize and standardize your script
- How to re-use your script
As we saw in Chapter 3, Seeing is Believing, Chapter 4, Data Sources, and Chapter 5, Data Modeling, the script editor is where a lot of the magic happens. In this chapter, we will be taking an in-depth look at the various functions that are available in this environment.
We will again be expanding the Airline Operations.qvw document that we worked on in the previous chapters. When you've opened the document, let's open the script editor by selecting File | Edit Script from the menu or by pressing Ctrl + E.
You will notice that the script editor consists of the following areas:
- A menu bar
- A toolbar
- A script pane
- A tool pane
The menu offers a wide range of options, for some of which the toolbar offers shorthand icons. For now, the most important options to take note of are:
The area that draws the most attention is the big, white area—the script pane. This is the main working area of the script editor. When a new QlikView file is created, the new script is populated with a Main tab and a few lines where number interpretation variables are added. These lines tell QlikView how to interpret various numbers and are generated automatically based on your operating system settings.
When looking further at the script pane we can see that the lines are numbered and that the editor has syntax highlighting. Based on their meaning, words in the script have a different color or font decoration. For example, we see that the word SET is shown in bold, blue text while the text immediately behind it is shown in italic, grey text. It is important to note that QlikView statements always end with a ; (semicolon).
In the previous chapters, we only used the Data tab of the tool pane.
When looking at the pane we will notice that there are some additional tabs:
- The Functions tab gives a categorized overview of all the script functions within QlikView. Further on, in this chapter we will have an in-depth look at some of these functions.
- The Variables tab shows user and system variables. This tab is populated after each reload, so in a new document it will be blank.
- The Settings tab contains some additional settings with regards to system access and password scrambling.
Now that we have had a first look at the script editor, let's get a little more hands-on and look at how we can create scripts.