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

[svinnakota]

Thank you Henric.

[yurgelmartina]

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&CD	Need	Stock CD	Status	Send Available
A	7	15	Available	7
B	20	0	Out of stock	0
C	30	20	Not available	20

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&STORE	Buffer Old	Buffer New	Status on CD	Increase
X	5	10	Available	5
Y	3	8	Not available	-
Z	2	6	Not 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&CD	Need	Stock CD	Status	Send Available
C	30	20	Not available	20

My question is:

Is that difficult to do?

Have you ever done or has some logic similar to?

Appreciate your help!

BR,

[hic]

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

[yurgelmartina]

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 Center	STORE
X1	S1
X1	S3
X2	S2

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.

SKU	Distributuion Center	SKU&CD	Need	Stock CD	Status	Send Available
A	X1	A/X1	7	15	Available	7
A	X2	A/X2	20	15	Available	0
B	X1	B/X1	30	20	Not available	20
B	X2	B/X2	7	20	Not available	7
C	X1	C/X1	20	0	Out of stock	0
C	X2	C/X2	30	0	Out of stock	20

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.

SKU	STORE	SKU&STORE	CD	KEY	Buffe Old	BufferNew	Status CD	Increase
A	S1	A/S1	X1	A/X1	5	10	Available	5
A	S2	A/S2	X2	A/X2	3	8	Available	-
A	S3	A/S3	X1	A/X1	3	8	Available	-
B	S1	B/S1	X1	B/X1	5	10	Not available	5
B	S2	B/S2	X2	B/X2	3	8	Not available	-
C	S1	C/S1	X1	C/X1	5	10	Out of stock	5
C	S2	C/S2	X2	C/X2	3	8	Out 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%

SKU	Distributuion Center	SKU&CD	Need	Stock CD	Status	Send Available
B	X1	B/X1	30	20	Not available	20
B	X2	B/X2	7	20	Not available	7
		TOTAL	37	20

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.

SKU	STORE	SKU&STORE	CD	KEY	Buffer Old	Buffer New
B	S1	B/S1	X1	B/X1	5	10
B	S2	B/S2	X2	B/X2	3	8

Thanks.

[Winter255]

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.

[hic]

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

[Winter255]

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

[novo_again]

Hi!

Are the any academic sources or books on the matter of Qlik scripting?

[Winter255]

The practice is the best way to learn )) Qlik has a great community to support beginners.