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

[hic]

Use a Concat(distinct [Person], ',') in a Load with a GROUP BY CID.

HIC

[#4]

Thanks Henric! That works.

[#5]

Hello Henri ,how would i procede for  a loop in my case as its a little different at least for me then your covered cases. Hope you can help Henric

I have in stage where  clauses  that start from producing a value  to 5 and every one from the 5 produces from 0 to n values and so on till condition not satisfied anymore .I did the first 2 that generated 1 and from 1 5 values but   3 one need something more complex way of doing.

Tip mişcare	Material	Lot	Comandă
261	2000322	0000076418	1069549
261	2000322	0000077372	1069549
261	2000322	0000075590	1069549
261	2000322	0000077840	1069549
261	2000778	0000078258	1069549

So need to run a where  with 3 condition tip miscare=101,lot  and  material .

Problem is for lot and material  as lot i have to run 5 wheres for every lot in up table that was generated fron secodn ehre clause .Also issues with material  that has  4 same and 1 diferent so 2 distinct values.

In sql i use in for multipel values for....

[#6]

Hi Henric,

Thanks for the post. I have a problem with looping: when a field has duplicate, the loop skips the duplicate line. For example:

TABLE:
load * inline
[A, B, C
China, Cotton, Shirt
India, Cotton, Socks
Japan, Leather, Jacket];

let vNOR=NoOfRows('TABLE');
for i=1 to $(vNOR)
let vA=FieldValue('A',$(i));
let vB=FieldValue('B',$(i));
let vC=FieldValue('C',$(i));

trace $(vA);
trace $(vB);
trace $(vC);

NEXT;

You can see in column B, line 1 and line 2 has duplicate values. When I run the script, the log is as below:

TABLE << INLFFD1 3 lines fetched
China
Cotton
Shirt
India
Leather
Socks
Japan

Jacket

The record "India-Cotton-Socks" became "India-Leather-Socks" while for Japan, the column B is missing. So I guess the loop skips the duplicates in column B, thus causing a problem with the data.

Could you kindly teach me how to fix this?

BR, PQ

[hic]

You cannot use the FieldValue() function here. It uses the symbol tables (see Symbol Tables and Bit-Stuffed Pointers) as input, and these store only the distinct values.

Instead, you should use Peek(), e.g.

let vA=Peek('A',i -1,'Table');

HIC

[#8]

The error is solved. Thanks a lot Henric!

BR, PQ

[#9]

Hi

I had to do two "For each X  in filelist(...)", and it works but it then loads the same file several times (more than two). Is that a common issue?

[hic]

Well... yes.

It happens to me often. But every time it does, it's because I've forgotten to change the file reference in the Load statement...

It should be

For each X  in filelist(...)

     Load ... From "$(X)" (...) ;

Next X

Apart from the above mistake, there should be no bugs here.

HIC

[#11]

hmm, it should work then, the reference for the second For Each ... and the Load * From statement should be $(vFile3) :

"

....

FOR i = 0 to NoOfRows('...')-1
LET vDir = Peek('...',$(iNode),'....');

For vFileNo = 1 to NoOfRows('....')-1
LET vNo = Peek('....',vFileNo-1,'....') ;

//Creata Condition(files) variable to load
FOR Each vFile in FileList('$(vDir)\*.txt')
  LET vCountNo = len(Keepchar(SubField('$(vFile)','\', SubStringCount('$(vFile)','\')+1),'0123456789'))+4 ;
  LET vTextFile = Left(SubField('$(vFile)','\', SubStringCount('$(vFile)','\')+1), len(SubField('$(vFile)','\', SubStringCount('$(vFile)','\')+1))-$(vCountNo));

  LET vFile2 = '$(vTextFile)$(vNo)' ;

//ILoad with condition variable 
FOR Each [vFile3] in FileList ('$(vNodeDir)\$(vFile2).txt')
  LET FileName = left(SubField('$(vFile3)','\', SubStringCount('$(vFile3)','\')+1),len(SubField('$(vFile3)','\', SubStringCount('$(vFile3)','\')+1)) -4);

Txtfile:
LOAD
*
FROM [$(vFile3)]
(txt, utf8, embedded labels, delimiter is '\t', msq)
;

store Txtfile into [..\Data\Staging\$(FileName).qvd] (qvd);
drop table Txtfile;


NEXT vFile3 ;
NEXT vFile;
NEXT vFileNo;
NEXT i ;  

....

"

?

[hic]

But with this structure, the same file will potentially be loaded several times...

You have 4 nested For-Each loops. The file name returned by the 3rd For-Each will be used several times by the 4th For-Each. Or?

Would it not be better to use a recursive approach, like (roughly)

Sub ScanFolder(vRoot)
For each vFile in Filelist( vRoot & '\*.txt') 
  Load * From [$(vFile)] (...);
Next vFile
For each vSubDirectory in dirlist( vRoot & '\*' )
  Call ScanFolder(vSubDirectory)
Next vSubDirectory
End Sub

Call ScanFolder('C:\Users\StartFolder')  

HIC