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]

Thanks for your reply. I think to use SQLTable I need to have the Office driver or package installed in our Publisher. That's something not allowed right now. Are there any other ways of reading the sheets from an xlsx dynamically. Anything like a match function with wildcard for example in a loop reading sheets starting with WorkBook* so that if the sheet names are WorkBook,Workbook1,WorkBooks1 then it should work.

Thanks,

Sudeep

[#4]

Hi Henric,

I have a table similar to the interval example given above. I would like to do exactly the same as the example, i.e., split it up to individual dates, but limiting the number of intervalIds to 1,2 and 3. In other words, I would like to ask if it possible to have both where clause and a while loop in the same load statement?

For Example:

Load a,b,c..

Resident X where a = '1'

while IterNo() < d;

Please note that the exact iteration variables are not important.

Your help is greatly appreciated!

Thyagesh

[hic]

Yes and No...

You cannot have a Where and a While in the same (syntactical) Load statement. But you can use a preceding load, i.e.

     Load * Where a = 'a';

     Load ... Resident ... While Iterno() < d;

or

     Load * While Iterno() < d;

     Load ... Resident ... Where a = 'a';

And by choosing which one you want to use, you also set the order of precedence between the Where and the While.

HIC

[#6]

Thank you very much Henric! This helped greatly!

I would just like to clarify, does this mean that the performance of the preceding load method is the same as not having the where clause?

Thyagesh

[hic]

A Where-clause always has a small penalty, as does a preceding load. But they are both small and this is by far the most efficient way to do this operation.

HIC

[#8]

Thank you very much for your help Henric!

[#9]

Thank u very much. It is really good.

Thanks and Regards

Amol Khochare

[Anonymous]

Very good post Henric!

I discovered, that the process of reading filelists with the command FOR EACH File IN FILELIST could be boring slowly, if the amount of files ist very large (hundreds of files). There is another, very fast way:

execute cmd.exe /c dir /s /b T:\*.csv > myfilelist.txt;

Filedata:

LOAD

    @1 as FilenamePath //Filename with Filepath

FROM myfilelist.txt (txt, codepage is 1252, no labels, delimiter is ';');

for i = 0 to  NoOfRows('Filedata')-1

    let iCSVFile = Peek('FilenamePath', i, 'Filedata');

    Data:

    LOAD

        *

    FROM [$(iCSVFile)] (txt, codepage is 1252, no labels, delimiter is ';', msq, header is 2 lines);

next //FilenamePath

(See Rapid directory/file scan

Regards

Michael

[hic]

Yes! Now your'e talking! I like that solution.

I have not tried your code myself, but if it is faster, I can only recommend to use this solution instead. At least until our developers speed up our internal "For Each" implementation...

HIC

[#12]

Hi Henric,

In looking at your great example abve I do have a question...From the example -   [Individual Skills]: it looks like you are creating 3 records from 1.  I need it done just the other way.  I have as many as 5 records with the same CID (caseID) with a person for each record.  I need to string the names out into one record with one CID.  Any ideas?

Thanks Mike McCrory