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);

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.

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

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.

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;

There are three possible values for the ErrorMode variable:

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 are interested in the total number of errors and their details, we can query the ScriptErrorCount variable, which has the number, and ScriptErrorList will have the text of the errors, separated by carriage returns.