When developing QlikView documents, we often have to apply the same set of logic or transformations to different data. I have often observed QlikView developers taking a copy-paste approach for re-using a script. While this approach may initially work, it does make the script a lot harder to maintain. When something needs to be changed, you need to change it in each instance of the script, running the risk of different versions of the same transformation process.
In this section, we will look at two better approaches for re-using data. The first is the use of subroutines, which can be used to re-use script within an document. The second is the use of include files, which enables re-use of script between different documents.
A subroutine is a reusable block of script that can be called from other places in the QlikView script by using the CALL statement. This block is formed using the SUB and END SUB control statements. Subroutines can contain parameters so that processing can be done in a flexible manner.
Note
As the QlikView script is processed in sequential order, the subroutine has to be defined before it can be called. Therefore, it is advisable to create subroutines as early as possible in the script.
When executing the script, everything between the SUB and END SUB control statements is ignored by QlikView. The subroutine is only run when it is called via the CALL statement.
A good example of a candidate for a subroutine is the trace statement that we used earlier to write the number of rows in a table to the Script Execution Progress window. Let's see how we can package this into a subroutine. As we want our subroutine to be flexible, we will add a parameter to specify the table that we want to show a row count for.
We will create this subroutine by following these steps:
- Open the script editor by pressing Ctrl + E.
- Create a new tab called Subroutines and place it immediately after the Main tab.
- Enter the following script.
SUB TraceRowCount (SourceTable) // Writes the number of rows in SourceTable // to the Script Execution Progress window. IF '$(SourceTable)' <> '' THEN LET vNoOfRows = NoOfRows('$(SourceTable)'); TRACE >>> Number of rows in $(SourceTable): $(vNoOfRows); LET vNoOfRows = Null(); ELSE TRACE >>> No table name specified; END IF END SUB - Go to the Aircrafts tab.
- Immediately after the first Aircraft Types load statement, enter the following statement:
CALL TraceRowCount('Aircraft Types'); - Add the same statement after the
LEFT JOIN ([Aircraft Types])statement, just above theDROP FIELDstatement.
Let's have a closer look at what this script does.
The subroutine is declared using the SUB control statement:
SUB TraceRowCount (SourceTable)
This tells QlikView that we want to declare a subroutine called TraceRowCount, which takes a single parameter: SourceTable. This parameter is passed into the subroutine as a variable, which only exists within the context of that subroutine.
The script checks if a value was given for the SourceTable parameter, if it has a value the number of rows is written to the Script Execution Progress window in the same way we saw earlier. If there is no value, an error message is returned.
The subroutine is ended using the End Sub statement.
Note
You may wonder why we used the LET vNoOfRows = Null(); statement. By default, variables that are created in script are also available on the frontend. To prevent this, we delete the variable by assigning it the value Null(). This approach does not work for variables that already exist, in that case you will first have to delete them manually from the Variable Overview (opened by pressing Ctrl + Alt + V in the frontend).
On the Aircrafts tab, we used the CALL TraceRowCount('Aircraft Types'); statement to show the number of records in the Aircraft Types table before and after joining the engine configuration information to it.
As we have seen in the previous section, we can use subroutines to re-use pieces of script within a QlikView document. It is also possible to re-use script between documents by including external script files. Re-using script between documents is a worthwhile goal as it eases development and simplifies maintenance.
We will see how we can take the row count subroutine that we created in the previous section and turn it into an included script file that we can use in all of our documents. Let's follow these steps:
- Open the Script Editor and navigate to the Subroutines tab.
- Select and copy the entire
TraceRowCountsubroutine to the clipboard by pressing Ctrl + C. - Open Notepad (by pressing Windows Key + R, typing in
notepadand pressing Return) or any other text editor. - Paste the
TraceRowCountsubroutine to Notepad. - Save the file to the same folder you used for the QlikView document and call it
TraceRowCount.qvs. - Close Notepad and return to QlikView's script editor window.
- Go to the Subroutines tab and remove the script for the
TraceRowCountsubroutine. - Select Tab | Rename from the menu and rename the tab to
Includes. - Select Insert | Include Statement and select the
TraceRowCount.qvsfile.
The resulting code should look like this:
$(Include=tracerowcount.qvs);
This statement tells QlikView to include the contents of the script file in the current script.
We've looked at how we can use include files to re-use script logic. As we will see in the next section, the same principle can also be applied to configuration settings such as file locations and database connection strings.
Tip
QlikView Components
Instead of creating your own library of scripts, you may also want to consider QlikView Components (Qvc). Qvc is a free, open source script library. Its mission is to implement scripting best practices, improve the speed and quality of script development, and create a common ground between script developers.
Qvc contains subroutines and functions to automate tasks of intermediate complexity, such as creating calendars, incremental loads, and the creation of link tables to support multiple fact tables.
Qvc can be downloaded from http://code.google.com/p/qlikview-components/