Qlik Community

Qlik Design Blog

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

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

47 Comments
Not applicable

Thx I migth use the scanfolder function.  Though I dont think your example helps me here?

Sorry, I did only count the "For" with the explicit Each after.

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

Well, yes and no I think. Its the same folder and the same files.

Perhaps I made it more advanced than I need:

The scope is that before this, I have a load statement with a field (which is restricted by a where clause), which gives me a list of numbers.

I need to loop (and store to qvd) only the files in the folder that ends with these numbers, so only where there is a match. The filenames consist of text and numbers. The numbers can be 1, 2 or 3 digits. That is where the $(vNo) is original peeked from, to get this list of numbers. In front of it is the text in the files: $(vTextFile).

So if the list of numbers from a loaded file is like 77, 88 an 99 and my file names in the folder I want to loop from is like:

Filename88.txt,

Filename99.txt,

Filename2.txt,

Samplename88.txt,

Samplename55.txt

- then when I store them into QVD I get:

Filename88.qvd,

Filename99.txt,

Samplename88.txt.

That is the result I need and so it works - kind of.

Because of the 3rd For-Each (vFile), it load files with "Filename" 3 times and "Samplename" 2 times.

Then the 4th For-Each will load only the ones I need, (or that is what I think it does... ). In the script execution process it looks like it is just overwriting (same name), but I checked the Qvd, I do end up with the correct one.

So sorry for my first Question/formulation, since it actually does what I ask it too.

This means that first I load all the files, to get the few I need, which seems a little stupid, but I could not make it with a simple where clause.

There must be a better way?

0 Likes
614 Views
atafsson
Contributor

Really good post (as always hic‌)

One function I miss though is the FieldValueList(). I find it very helpful when having a loaded table with values I want to use in the loop. This way I don't need the peek() function to find a specific value for each iteration, the FieldValueList() function makes that for me.

For example I've used it for first loading a system table from a SQL database, filtering out the views from a Data Warehouse. Then use the table name like this:

TableNames_tmp:

Load TableName

From SQL_sytemtable

Where "filter for finding the needed views";

For each vTableName in FialdValueList('TableName')

$(vTableName😞

Load *

From SQL_Servername.$(vTableName)

;

Store $(vTableName) into ...\$(vTableName).qvd;

The function has pretty bad documentation but works really good when looping over values from a previous load table in Qlik.

hic‌ is there a reason for the lack of documentation and is it not "recommended" to use this function?

Thanks

0 Likes
614 Views
beck_bakytbek
Honored Contributor

super!

thanks a lot

0 Likes
614 Views
Chanty4u
Esteemed Contributor III

Hi HIC,

Henric Cronström

I have same issue      but with different  files in same format  how can i achieve this?

i need to get  with the same files into one table?

RE: Keepchar

0 Likes
614 Views
berryandcherry6
Contributor II

HI Henric,

For my latest requirement, i need to build my data model, in such a way that, it should contain a table(Virtual or master table) which contains data from multiple table  and get some counts or sum of filtered rows from table and dump into this master or virtual table.

I have posted here

Check this link: creating Virtual Table in load script in qlik

My Using Tables, currently consists of following columns.

CourseCompletion Table

course_completion_id,course_id,user_id,is_new_user,certificate_id,certificate_access_log_id,batch_meta_data_idinvitation_idlender_offer_idpercentage_scorefinal_test_statustest_attempt_countcourse_completedfinal_test_appeared_datedate_completeddate_createdsurvey_completedis_feedback_completed

Signup table

signup_activity_log_id,invitation_id,client_id,user_id,batch_meta_data_id,rc_activation_code,referrer_email_type_iduser_signeduser_agentreferrerdate_accesseddate_signedip_addresslanding_page_accessed

Invitation Table

invitation_id,user_id,client_id,batch_meta_data_id,first_name,last_name,email,custom_field_1,custom_field_2custom_field_3Street1Street2cityzip_codetelephoneStateclient_unique_idrc_activation_codeshorter_activation_coderc_activation_linkactivateddate_activateddate_createdcreated_bycampaign_idlanguage_id

Things i need to know:

1.) As you can see in course completion table, there it only consists of batch_meta_data_id, for this while building virtual table i need to identify its client in looping to which it belongs, how to do this?

2.)Using loop technique, i only find looping of single tables, how could i loop througn multiple tables data, and dump into virtual table. How should i approch this?

3.)Through looping i need to get count or sum of filtered datas, how to get sum or count of those?

Could you provide some outline code for this, as i found only single table data looping every where. How should i approach to get required table to achieve requirement. Please help me on this.

comments on this is highly appreciated

0 Likes
614 Views
paulyeo11
Valued Contributor II

Hi All

I am direct to this blog.

May i know how to make use of Loop command and load 100 raw data set ?

And i know this provide me the right solution. The issue is i don't know how to start. Hope some one can give me kick start. As i see so many expert is here all know.

Paul

0 Likes
614 Views
svinnakota
Contributor

Hi Henric,

Thanks for the post. I have question with respective to the performance of looping and individual sections code. In my script i am having the separate section for 10 tables and the code is almost similar in each section with additional deriving columns with respect to the table.

So if i want to implement the same code in the for loop to avoid code redundancy, i need to keep additional if conditions in the for loop for each table. Can you please suggest me which is faster in loading data, is it individual sheet code or is it the for loop?. The data will be like 10 lakh records in each table.

Thanks in advance.

Regards,

Santosh

0 Likes
614 Views

There really shouldn't be a ny significant difference in script execution time. Since the tables are large, the execution time of the additional IF statements is negligeble.

HIC

PS I had to google "lakh". Did not know what it was 🙂

0 Likes
614 Views
svinnakota
Contributor

Sorry for the trouble. can you please suggest me what is the better way for writing the code in loading hundred thousand records without code redundancy. And if the data is small then, is looping faster than individual script??

Regards,

Santosh

0 Likes
614 Views

As I said, there should be no significant difference in execution time when you compare a loop with repeated code. So to avoid code redundancy, I would use a loop or a subroutine.

HIC

0 Likes
614 Views
svinnakota
Contributor

Thank you Henric.

0 Likes
614 Views
yurgelmartina
New Contributor

Hello Henric,

I have a problem, I will try to explain it to you to see if you can help me:

I have the amount in stock in a CD and there are some needs of increase the buffers.

So I have a full database and grouped by CD and SKU to see the total amount that are in the CD.

There are some cases that it is possible to change but other cases no.

Below you can see it:

SKU&CDNeedStock CDStatusSend Available
A715Available7
B200Out of stock0
C3020Not available20

When I go back to the table SKU & Store I can see which CD have the amount avaiable as below:

When it is avaible is easy and I can do a simple subtraction to see how much I need to increase.

SKU&STOREBuffer OldBuffer NewStatus on CDIncrease
X510Available5
Y38Not available-
Z26Not available-

But in the cases it is not avaible I would have to do a looping - DO while I think to see the target I can fullfill.

For example in this hypothetical  example I need 30 but have only 20.

Then I would need to distribute the 20 to the SKU&Location till it reaches the 20.

I have a criteria as best sellers that would not be a problem.

SKU&CDNeedStock CDStatusSend Available
C3020Not available20

My question is:

Is that difficult to do?

Have you ever done or has some logic similar to?

Appreciate your help!

BR,

0 Likes
614 Views

I'm not sure I understand the problem completely, but ...

Can you not - instead of looping - just calculate the number you can send using

   RangeMin( [Need], [Stock CD]) as [Send Available]

This expression will always return the smaller of the two values, and this is the number you can/should send.

HIC

0 Likes
614 Views
yurgelmartina
New Contributor

Hi Henric,

Actually no because I do not want to chose the min value.

Let me try to explain again:

I have a distribution Center that fulfill some store ( in the real case is 3 for 303 stores but I am tryinf to make it simple to make the logic with your help )

Distributuion CenterSTORE
X1S1
X1S3
X2S2

I have a demand to supply the Stores but need to see if the Distribution Center has capacity.

*Here it is an example with random numbers.

I have how much we have in the CD of each SKU.

If Stock CD > What I need then it is available.

If Stock CD = 0, then out of stock.

Else: Not available.

SKUDistributuion CenterSKU&CDNeedStock CDStatusSend Available
AX1A/X1715Available7
AX2A/X22015Available0
BX1B/X13020Not available20
BX2B/X2720Not available7
CX1C/X1200Out of stock0
CX2C/X2300Out of stock20

That is fine the logic for me.

Note that now we have the Key SKU Store.

Then I can do an apply map because I know what are the CD that fullfil the Store.

*Random numbers again, they are not related.

   

SKUSTORESKU&STORECDKEYBuffe OldBufferNewStatus CDIncrease
AS1A/S1X1A/X1510Available5
AS2A/S2X2A/X238Available-
AS3A/S3X1A/X138Available-
BS1B/S1X1B/X1510Not available5
BS2B/S2X2B/X238Not available-
CS1C/S1X1C/X1510Out of stock5
CS2C/S2X2C/X238Out of stock-

In the cases that it is available it is ok, I have the capacity to attend the demand.

My issue is:

The cases when it is not available I have an amount that I can distribute to the stores but not 100%

 

SKUDistributuion CenterSKU&CDNeedStock CDStatusSend Available
BX1B/X13020Not available20
BX2B/X2720Not available7
TOTAL3720

I have the need to send 37 but only 20 in my CD.

Then I need to fullfill it to the stores and Skus according to the keys.

My question is if I can do a looping to fullfill it till the 20 based on a criteria.

 

SKUSTORESKU&STORECDKEYBuffer OldBuffer New
BS1B/S1X1B/X1510
BS2B/S2X2B/X238

Thanks.

0 Likes
614 Views
Winter255
New Contributor

Hello  HIC

I'm quite new to qlik.

I need to set up upload of daily data from remote web address ending with ...=YYYYMMDD

I need data for 2 months day by day.

So far I have this :

 

SET vStartDate = 2017.03.01;
SET vEndDate = 2017.04.30;
SET DateFormat ='YYYY.MM.DD';

Table:
LOAD Date(StartDate+IterNo()-1) as Date
While StartDate+IterNo()-1 <= EndDate;
LOAD
Min(StartDate) as StartDate,
Max(EndDate) as EndDate
Inline [
StartDate, EndDate
'$(vStartDate)', '$(vEndDate)'
]; // Decided to fill in table with dates and then parse it to address.

for vDateNr =1 to NoOfRows('Table')

let vDate = Peek(Num(Date#('Date'),'YYYYMMDD'),vDateNr-1,'Table');
Rates:
LOAD
DATE('$(vDate)') as Date,
Property,
Price
FROM
[http://.....=$(vDate)]
(txt, utf8, no labels, delimiter is '\t', msq);
NEXT vDateNr

 

Coul you please advice the correct approach.

 

 

0 Likes
587 Views

Your solution should work. I cannot see any problem with it.

But I would probably go for a simpler solution, like

For vDateNr = 0 to 60 Step -1
   Let vDate = Text(Date(Today()-vDateNr,'YYYYMMDD'));
   Load Date#('$(vDate)','YYYYMMDD') as Date, Property, Price
      From [http://.....=$(vDate)] (txt, utf8, no labels, delimiter is '\t', msq);
Next vDateNr

0 Likes
575 Views
Winter255
New Contributor

It works but processes in a wrong way.

There are 32 rows for Property, and Price cells values per date Nr1 . 

So it fills only First 32 rows with 'date  Nr1'  and  data For Property an Price and then fills rest with  dates  (one row of each) and leaving Blank values for Property and Price

 

0 Likes
571 Views