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.
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:
Let's have a closer look at what this script does.
The subroutine is declared using the SUB control statement:
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.
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.
Including script files
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:
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.
In this example, we put the script file in the same folder as the QlikView document. In a real environment, we would set up a folder structure with an include or library folder that contains all reusable scripts.
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.
Qlik View Related Interview Questions
|Microstrategy Interview Questions||IBM Cognos Interview Questions|
|PL/SQL Interview Questions||MSBI Interview Questions|
|VBA For Excel Interview Questions||SAP BO Interview Questions|
|SQL Database Interview Questions||Qlik View Interview Questions|
|R Programming language Interview Questions||Pentaho Interview Questions|
|Advanced SAS Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.