Loops in the Script

[hic]

Iterations – or loops – are constructions where a set of statements are executed zero or more times, until some condition is met. They are very common in all programming languages, and QlikView scripting is no exception.

[hic]

First of all, the Load statement is in itself a loop: For each record in the input table, the field values are read and appended to the output table. The record number is the loop counter, and once the record is read, the loop counter is increased by one and the next record is read. Hence – a loop.

But there are cases where you want to create other types of iterations – in addition to the Load statement.

For - Next Loops

Often you want a loop outside the Load statement. In other words; you enclose normal script statements with a control statement e.g. a "For…Next" to create a loop. An enclosed Load will then be executed several times, once for each value of the loop counter or until the exit condition is met.

The most common case is that you have several files with the same structure, e.g. log files, and you want to load all of them:

   For each vFileName in Filelist ('C:\Path\*.txt')
      Load *,
         '$(vFileName)' as FileName
      From [$(vFileName)];
   Next vFileName

Another common case is that you already have loaded a separate table listing the files you want to load. Then you need to loop over the rows in this table, fetch the file name using the Peek() function, and load the listed file:

   For vFileNo = 1 to NoOfRows('FileListTable')
      Let vFileName = Peek('FileName',vFileNo-1,'FileListTable');
      Load *,
         '$(vFileName)' as FileName
      From [$(vFileName)];
   Next vFileNo

Looping over the same record

You can also have iterations inside the Load statement. I.e. during the execution of a Load statement the same input record is read several times. This will result in an output table that potentially has more records than the input table. There are two ways to do this: Either by using a While clause or by calling the Subfield() function.

One common situation is that you have a table with intervals and you want to generate all values between the beginning and the end of the interval. Then you would use a While clause where you can set a condition using the loop counter IterNo() to define the number of values to generate, i.e. how many times this record should be loaded:

   Dates:
   Load
      IntervalID,
      Date( FromDate + IterNo() - 1 ) as Date
      Resident Intervals
      While IterNo() <= ToDate - FromDate + 1 ;

Another common situation is that you have a list of values within one single field. This is a fairly common case when e.g. tags or skills are stored, since it then isn’t clear how many tags or skills one object can have. In such a situation you would want to break up the skill list into separate records using the Subfield() function. This function is, when its third parameter is omitted, an implicit loop: The Load will read the entire record once per value in the list.

   [Individual Skills]:
   Load
      [Employee No],
      SubField(Skills, ',') as Skill
      Resident Employees;

Bottom line: Iterations are powerful tools that can help you create a good data model. Use them.

HIC

[sudeepkm]

Hi Henric,

Thanks a lot for posting such an informative and useful topic.

Currently I've an assignment where I'm trying to load data from multiple sheets of multiple xlsx.

I'm using two for loops for reading files and then the sheets

it seems reading sheets by using sheet number is somehow not working for XLSX (but works for XLS properly) and QlikView is throwing an error when I'm using sheet number to read sheet from an XLSX.

For some reason I cannot use ODBC XLS load or the Sheet Names

Given below is the code I'm using.

for each vFiles in filelist('$(vDataFolder)*.xls')

for i = 1 to 3

Load A

FROM [$(vFile)]

            (ooxml, no labels, table is @$(i))

next

next

Can you please provide any suggestions.

Thanks and Regards,

Sudeep

[paul_scotchford]

Nice article Henric, thanks

[philip_doyne]

Dear Sudeep

I would put the sheet name in a variable in the loop

eg:

LET vSheet = 'Sheet' & '$(l)';

and then use $(vSheet) in the FROM parameters

That way you can use Debug to see if you have got the sheet name right

There is often confusion as to whether you need to put $(i) inside '   '   or not,

Philip

[sudeepkm]

Hi Philip,

Thanks for your reply. Unfortunately I cannot rely on sheet names because they vary.

there will be two or three sheets with names starting as "AboutData" from where I'll pull data.

Are there any option to load data from multiple sheets based on SheetName wild card.

For example if I've three sheets as AboutData, AboutD_1 and AboutD_2 then can I use any for loop to loop through only those sheets with name starting with About*.

Please let me know if you have any suggestions.

Thanks and Regards,

Sudeep

[#7]

Hi Henric,

This post is well timed. I would like to use a loop that drives my sql load based on date ranges. I am not certain how to set up the variable loop and have SQL recognize it.

Let vDate = 1/1/2013

Load

a,b,c;

SQL Select *

     From table

     where date = $(vDate);

Any thoughts?

[#8]

Hi

I believe you are look for the below;

Let vStartDate = 1/1/2013

Let vStartDate = 1/12/2013

Load

a,

b,

c;

SQL Select *

     From table

     while $(vStartDate) + IterNo() - 1 <= $(vEndDate);

I hope that helps.

Steve

[hic]

It all depends on how you want to make the where clause in the SELECT statement. The syntax depends on which DB it is. usually, you want a string that contains a date format, e.g 1/1/2013, If so, you should use

Set vDate = 1/1/2013 ;

Don't use "Let". Then you will get 1 divided by 1 divided by 2013.

For the SELECT statement, you need to check the SQL syntax. The QlikView syntax and functions are not applicable.

HIC

[dirk1955]

Hi,

I think you can use Let but then:

Let vDate = '1/1/2013' ;

Or am i wrong?

DDW

[hic]

You are right. The two following statements are equivalent:

Set vDate = 1/1/2013 ;

Let vDate = '1/1/2013' ;

You just have to make sure that the Let statement does not calculate a numeric value.

HIC

[Clever_Anjos]

Yes, Sudeep Mahapatra,

Use SQLTAbles command to discover all sheet names.

An example:

Let vWorkBook= '..\xls\Carga SIM Multiplas abas.xlsx';

ODBC CONNECT32 TO [Excel Files;DBQ=$(vWorkBook)];

tables:

SQLtables; 

Tmp:

LOAD

  TABLE_NAME

Resident tables

where TABLE_NAME like 'About*';