Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee
Employee

Loops in the Script

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.


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.

Files.pngFor - 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

Files Table.pngAnother 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 ;

Intervals with Arrows.png

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;

Skills w Arrows.png

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

HIC

44 Comments
sudeepkm
Valued Contributor III

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

0 Likes
25 Views
paul_scotchford
Valued Contributor

Nice article Henric, thanks

0 Likes
25 Views
philip_doyne
Contributor II

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

0 Likes
25 Views
sudeepkm
Valued Contributor III

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

0 Likes
25 Views
Not applicable

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?

0 Likes
25 Views
Not applicable

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

0 Likes
25 Views
Employee
Employee

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

0 Likes
25 Views
dirk1955
Contributor

Hi,

I think you can use Let but then:

Let vDate = '1/1/2013' ;

Or am i wrong?

DDW

0 Likes
25 Views
Employee
Employee

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

0 Likes
25 Views
Employee
Employee

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*';

0 Likes
25 Views
sudeepkm
Valued Contributor III

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

0 Likes
25 Views
Not applicable

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

0 Likes
25 Views
Employee
Employee

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

0 Likes
25 Views
Not applicable

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

0 Likes
25 Views
Employee
Employee

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

0 Likes
25 Views
Not applicable

Thank you very much for your help Henric!

0 Likes
25 Views
Not applicable

Thank u very much. It is really good.

Thanks and Regards

Amol Khochare

0 Likes
25 Views
michaelk
Contributor

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

25 Views
Employee
Employee

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

25 Views
Not applicable

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

0 Likes
25 Views
Employee
Employee

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

HIC

25 Views
Not applicable

Thanks Henric! That works.

0 Likes
25 Views
Not applicable

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şcareMaterialLotComandă
261200032200000764181069549
261200032200000773721069549
261200032200000755901069549
261200032200000778401069549
261200077800000782581069549

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....

654776586.pngstrucutre i want i subfoder style showing by click stage for every brach persued by clicking.png

0 Likes
25 Views
Not applicable

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

0 Likes
25 Views
Employee
Employee

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

25 Views
Not applicable

The error is solved. Thanks a lot Henric!

BR, PQ

0 Likes
25 Views
Not applicable

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?

0 Likes
25 Views
Employee
Employee

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

25 Views
Not applicable

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 ;  

....

"

?

0 Likes
25 Views
Employee
Employee

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

0 Likes
25 Views