Any basic development language will include some control structures to either repeat the execution of particular tasks or change what task will happen next based on conditions. QlikView is no different, so in this section we will examine the various options.
It can be enormously important to be able to execute different sets of statements based on different conditions. It gives us a lot of flexibility in implementing our solutions.
If ... Then ... ElseIf
is a fairly fundamental construct in many programming languages. We test a condition, and if it is true, we execute one set of statements. If it isn't true, then we can either execute a different set of statements or perform a new test and keep going.
As an example, if we wanted to test whether a file exists before trying to load it:
If Alt(FileSize('c: empData.qvd'),0)>0 Then Data: Load * From c: empData.qvd (qvd); End if
If, instead of not doing anything, we want to load a different file, then we might do this:
If Alt(FileSize('c: empData1.qvd'),0)>0 Then Data: Load * From c: empData1.qvd (qvd); ELSE Data: Load * From c: empData2.qvd (qvd); End if
Of course, we should really check whether this second file exists:
If Alt(FileSize('c: empData1.qvd'),0)>0 Then Data: Load * From c: empData1.qvd (qvd); ELSEIF Alt(FileSize('c: empData2.qvd'),0)>0 Then Data: Load * From c: empData2.qvd (qvd); ELSE Trace We have no files to load!!!; End if
There are several functions in QlikView that return a Boolean result. For example, the YearToDate
function accepts a date, and some other parameters and will return true
or false
if that date is in the year-to-date. Unlike other languages, QlikView does not actually have a Boolean type. Instead, Boolean functions will return an integer value—0
for false and -1
for true. In fact, as far as any condition in QlikView is concerned, 0
is always false and anything that is not 0
means true.
This means that there are several other functions that might not be considered to be strictly conditional and can be used as conditional functions. Any function that returns 0
as an indication of a failure to perform and a nonzero value when it succeeds can be used as a conditional function.
For example, the Index
function returns the position of a substring in another string. If it fails to locate the substring, then it will return 0
. We might think that we should use this in a condition like this:
Let vText='ABCDEFG'; Let vSearch='ABC'; If Index(vText, vSearch)>0 Then Trace Found $(vSearch) in $(vText); End if
However, as the fail condition returns 0
, we can just write the If
statement like this:
If Index(vText, vSearch) Then Trace Found $(vSearch) in $(vText); End if
There are a few other functions that return 0
. If a function, such as FileSize
, returns a null
value for a fail, we can turn this into a zero by wrapping it in the Alt
function as we did just now. In this case, we included the >0
test, but we could have written the If
statement without it:
If Alt(FileSize('c: empData.qvd'),0) Then Data: Load * From c: empData.qvd (qvd); End if
Switch ... Case
is a less frequently used construct than If … Then … ElseIf
; this will be familiar to C/Java programmers. We test a value and then present several possible options for that value and execute script blocks if there is a match. We can also specify a default if there are no matches.
Here is a very simple example; note that we can pass several values to each Case
statement:
Let vVal='Hello'; SWITCH vVal CASE 'Hello','Hi' Trace Hello there!; CASE 'Goodbye','Bye' Trace So long!; DEFAULT Trace Glad you are staying; END Switch
When
and Unless
are the equivalent of a single
If … Then
statement. They usually appear as prefixes to a valid statement, but there are some control statements that can have them as suffixes. The statement is followed by a conditional test and then by the statement to execute if the condition is true or false. Consider this example:
When Alt(FileSize('c: empData2.qvd'),0) > 0 Load * from c: empData2.qvd (qvd);
An example of Unless
is:
Unless Alt(FileSize('c: empData1.qvd'),0)=0 Load * from c: empData1.qvd (qvd);
Repeating a step several times is something that we will have to do again and again. There are a number of ways of performing loops, depending on requirements.
AutoGenerate might not be called a loop by some people but it does actually perform a repeating task, the generation of multiple rows, for a set number of iterations. The statement takes one parameter: the number of rows to generate.
Generating empty rows is not very useful, so we need to combine this AutoGenerate
with a function such as RecNo()
or RowNo()
and other calculations based on them. Often both functions are interchangeable because the number of rows generated as source will usually be the same as the number actually loaded. However, if we are going to use a preceding load, then we will need to use RecNo()
as the RowNo()
function will return zeroes.
Anywhere that we require to create a sequential list of values, we can think of perhaps using AutoGenerate
. A great use case is the generation of a calendar table:
// Calendar starts on the 1st January 2010 Let vStartDate=Floor(MakeDate(2010,1,1)); // Calendar ends on the last day of last month Let vEndDate=Floor(MonthStart(Today()))-1; // How many rows do we need to generate? Let vDiff=vEndDate-vStartDate+1; // Generate the calendar table Calendar: Load TempDate as DateID, Year(TempDate) As Year, Month(TempDate) As Month, 'Q' &Ceil(Month(TempDate)/3) As Quarter, Day(TempDate) As Day, Week(TempDate) As Week, Date(MonthStart(TempDate), 'YYYY-MM') As YearMonth, -YearToDate(TempDate, 0, 1, $(vEndDate)) As YTD_Flag, -YearToDate(TempDate, -1, 1, $(vEndDate)) As LYTD_Flag; // Generate the number of rows required Load RecNo()-1+$(vStartDate) As TempDate AutoGenerate($(vDiff));
This script will generate the number of rows between two dates and use the start date as the first value and increment this by one for each subsequent row. The preceding load then transforms the TempDate
field into various date values.
Creation of a calendar like this might be a piece of script that you store in a separate text file for inclusion in several QlikView applications.
The For ... Next
type of loops are one of the most common in many programming languages. We assign an initial value to a variable, perform a sequence of statement, increment the variable by a fixed amount, then repeat until we have reached the end point.
Here is a very simple example:
For i = 1 to 10 Trace The value of i is $(i); Next
This will show in the script execution dialog like this:
This loop started at 1
, echoed the value to the screen, incremented by the default step of 1
, and then repeated until it executed for the last value of 10
.
If we want to use a step other than the default, we can add the Step
to the For
statement:
For i = 5 to 50 Step 5 Trace The value of i is $(i); Next
We can even go backwards:
For i = 10 to 1 Step -1 Trace The value of i is $(i); Next
The variable that is generated can be used anywhere that we might use a variable normally in QlikView. Consider this example:
For vYear=2010 to Year(Today()) Data: SQL Select * From Data Where Year=$(vYear); Store Data into $(vQVDPath)Data$(vYear).qvd; Drop Table Data; Next
This script will generate separate QVD files for each year from 2010
to the present year.
We can also nest loops inside each other:
For x = 1 to 10 For y = 1 to 10 Matrix: Load $(x) As X, $(y) As Y AutoGenerate(1); Next Next
Not every loop that we want to make will be based on a sequence of number. The For Each
syntax allows us to use a list of any values that we assign:
For Each vVar in 'A','B','C' Data: SQL Select * From Table$(vVar); Store Data into $(vQVDPath)Table$(vVar).qvd; Drop Table Data; Next
We can even derive the list of values from the data:
Temp: Load Chr(39) &Concat(Field1,Chr(39)&','&Chr(39)) &Chr(39) As Temp_Field Resident Table1; Let vList=Peek('Temp_Field'); Drop Table Temp;
There are two filesystem-related functions that we can also use with For Each
—FileList
and DirList
.
The FileList
function takes a file mask using wildcards and will return a list containing the full file path of all files that match. We can then loop through that list with For Each
and process them. Have a look at this example:
For Each vFile in FileList('c:data*.csv') Let vFileLen=FileSize('$(vFile)'); Let vFileDate=FileTime('$(vFile)'); Trace $(vFile) $(vFileLen) $(vFileDate); Data: LOAD * FROM [$(vFile)] (txt, utf8, embedded labels, delimiter is ',', msq); Next
The DirList
function is similar to FileList
except that it returns a list of folders instead of files. This function is very often used with a nested FileList
. The following is an example:
For Each vFolder in DirList('c:data*') For Each vFile in FileList('$(vFolder)*.csv') Let vFileLen=FileSize('$(vFile)'); Let vFileDate=FileTime('$(vFile)'); Trace $(vFile) $(vFileLen) $(vFileDate); Data: LOAD * FROM [$(vFile)] (txt, utf8, embedded labels, delimiter is ',', msq); Next Next
Another very common construction in programming is the Do … Loop
statements, which cause a block of script to be executed either while a condition is fulfilled or until a condition is fulfilled:
Let vLetters='ABCDEFGHIJKLMNOPQRSTUVWXYZ'; Do Load Left('$(vLetters)',1) As Letter, RowNo() As LetterIndex AutoGenerate(1); Let vLetters=Mid('$(vLetters)', 2); Loop Until Len('$(vLetters)')=0
We can also write this by putting a clause at the beginning:
Do While vLetters<>'' Load Left('$(vLetters)',1) As Letter, RowNo() As LetterIndex AutoGenerate(1); Let vLetters=Mid('$(vLetters)', 2); Loop
The difference is that a clause at the beginning means that there is potential for the script block to never execute. A clause at the end means that the block will execute at least once.
There are a few different circumstances in which we might want to break the execution of the script or a block of script.
We can exit the entire script by calling the function:
Exit Script;
The script will terminate normally at this point, as if there were no additional script lines following it.
This can be an enormously useful thing for us to insert into our script to test and troubleshoot. By adding the function at any stage in our script, we can then find out what state our data is in.
We can enhance the troubleshooting functionality by adding a condition to the exit. We can use an If … Then
construct, but this is also a case where our conditional functions, When
and Unless
, can be appended. For example, if we want to stop our script unless some condition is true, the following code can be used:
EXIT Script when FieldValueCount('Letter')<>26;
This can also be written like this:
EXIT Script unless FieldValueCount('Letter')=26;
As another example, we might want the script to end at a certain point unless it is the first day of the month:
EXIT Script unless Day(Today())=1;
We can also exit other script constructs such as For
/For Each
and Do
loops and subroutines. The syntax is similar to the aforementioned, but we just need to use the correct keyword for the construct that we are in:
Exit For; Exit Do; Exit Sub;
We can also append conditional expressions:
Exit For when vFound=1;
Rather than allowing QlikView to throw an error and stopping the execution of a script, there are a number of variables that we can use to handle error situations and allow the script to continue.
There are three possible values for the ErrorMode
variable:
ErrorMode |
Description |
---|---|
|
QlikView will ignore any errors. The script execution will continue at the next line of script. |
|
This is normal error handling. The script will halt and the user will be prompted for an action. |
|
In this mode, the user will not be prompted and the script will fail as if the user clicked on Cancel on the prompt dialog. |
To turn off error handling, we simply set the variable as follows:
Set ErrorMode=0;
To turn it back on, we set the variable again:
Set ErrorMode=1;
If we turn off error handling, we will need to do our own error handling by regularly querying the state of the ScriptError
variable.
The ScriptError
variable will contain a dual value with the error code as the number and the description as the text. If the error code is zero, then there is no error.
Some of the database errors will generate additional error messaging in the ScriptErrorDetails
variable.