Qlik Community

Ask a Question

Qlik Design Blog

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

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
Henric_Cronström

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

49 Comments
svinnakota
Creator
Creator

Thank you Henric.

0 Likes
1,195 Views
yurgelmartina
Contributor
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
1,195 Views
Henric_Cronström

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
1,308 Views
yurgelmartina
Contributor
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
1,308 Views
Winter255
Contributor II
Contributor II

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
1,281 Views
Henric_Cronström

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
1,269 Views
Winter255
Contributor II
Contributor II

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
1,265 Views
novo_again
Contributor
Contributor

Hi!

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

496 Views
Winter255
Contributor II
Contributor II

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

0 Likes
363 Views