Loops in Qlik Sense Script: FOR, FOR EACH, WHILE, and DO WHILE

[Assel_Dochshanova_Pazylbekova]

When working with large datasets, loading everything in a single query is rarely an option. When the source can't return all the data at once, you need to break the load into steps: by date, by region, by file. That's exactly what loops are for in Qlik Sense script. They reduce code volume, make the load process manageable, and allow you to build incremental ETL pipelines that refresh only the required data slice without overloading the source.

Qlik Sense has four types of loops, and each has its own use case.

FOR..NEXT: when the number of iterations is known in advance. The period is defined through a variable storing the difference between dates. Since the counter starts at 0, we use -1 to avoid an extra iteration:

LET vDaysCount = Date#(vEndDate,'YYYY-MM-DD')
               - Date#(vStartDate,'YYYY-MM-DD');

FOR i = 0 TO $(vDaysCount) - 1
    LET vDate = Date(Date#(vStartDate,'YYYY-MM-DD') + i, 'YYYY-MM-DD');
    // query body
NEXT i

FOR EACH..NEXT: iterates over a list of values: regions, file names, product codes. The list can be defined explicitly or generated using functions like FileList(), DirList(), or FieldValueList():

FOR EACH vRegion IN 'UZB', 'KAZ', 'RUS'
    LOAD * FROM [lib:///_$(vRegion).qvd](qvd);
NEXT vRegion

WHILE: used inside a LOAD statement together with AutoGenerate and the IterNo() function. The classic example is generating a Master Calendar:

TempCalendar:
LOAD Date($(vMinDate) + IterNo() - 1, 'YYYY-MM-DD') as tDate
AutoGenerate 1
While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

The condition is evaluated on each row via IterNo(): this is row generation inside a single LOAD statement.

DO WHILE: a loop with an updatable variable. The condition is re-evaluated before each iteration. Used for incremental loading from an external source with a date-by-date breakdown:

LET vEndDate       = Date(Today(), 'YYYY-MM-DD');
LET vStartDate     = Date(Today() - 7, 'YYYY-MM-DD');
LET vIterationDate = vStartDate;

DO WHILE vIterationDate <= vEndDate

    // query body
    WHERE date_field = toDate('$(vIterationDate)')

    STORE TempOneDay INTO [lib://…/data_$(vQVDName).qvd](qvd);
    DROP TABLE TempOneDay;

    LET vIterationDate = Date(Date#(vIterationDate,'YYYY-MM-DD') + 1, 'YYYY-MM-DD');
LOOP