Wes' Public Vault

Qlik Loops - For Loop

2 min read

Within Qlik Loops, the For Loop is meant for

  • Iterating over a list of items with a known length
  • Iterating a set number of times
  • When you have a Defined Beginning and a Defined Ending

Basic For Loop

// Basic For Loop with TRACE statements
FOR vLoop = 1 TO 5
 
    TRACE vLoop: $(vLoop);
    
NEXT

For Loop with Variables

// Define loop control variables
SET vStartYear = 2020;
SET vEndYear = 2023;
SET vYearStep = 1;
 
// Using variables to control the loop
FOR vYear = $(vStartYear) TO $(vEndYear) STEP $(vYearStep)
 
    TRACE Processing data for year $(vYear);
 
    // [Sales_$(year)]:
    // LOAD
        // OrderID,
        // CustomerID,
        // Amount,
        // $(year) as Year
    // FROM [lib://Sales/Sales_$(year).qvd];
 
NEXT

NoOfRows() and Peek()

 
// Create an INLINE table of parameters
RainbowColors:
LOAD * INLINE [
Color
Red
Orange
Yellow
Green
Blue
Indigo
Violet
];
 
// Loop through the INLINE table
FOR vLoop = 0 TO NoOfRows('RainbowColors') - 1
 
    LET vColor = Peek('Color', $(vLoop), 'RainbowColors');
    TRACE Color: $(vColor);
 
NEXT

FOR EACH item IN FieldValueList(‘Table’)

FieldValueList() takes in a field name as an argument

FOR EACH color IN FieldValueList('Color')
 
	TRACE color = $(Color);
 
NEXT

Nested For Loop

Years:
LOAD * INLINE [
    Year
    2022
    2023
    2024
];
 
Months:
LOAD * INLINE [
    Month, MonthName
    01, January
    02, February
    03, March
    04, April
    05, May
    06, June
    07, July
    08, August
    09, September
    10, October
    11, November
    12, December
];
 
Products:
LOAD * INLINE [
    Product
    Electronics
    Furniture
    Clothing
    Appliances
    Groceries
];
 
// First level loop - iterate through Years using FOR EACH with FieldValueList
FOR EACH vYear IN FieldValueList('Year')
    
    TRACE Processing Year: $(vYear);
    
    // Second level loop - iterate through Months using FOR EACH with FieldValueList
    FOR EACH vMonth IN FieldValueList('Month')
        
        // Third level loop - iterate through Products using FOR EACH with FieldValueList
        FOR EACH vProduct IN FieldValueList('Product')
            
            // Construct file name based on pattern
            LET vFileName = '$(vYear)_$(vMonth)_$(vProduct)_Sales.csv';
            TRACE Attempting to load: $(vFileName);
 
        NEXT vProduct
        
    NEXT vMonth
    
NEXT vYear

Exiting a loop early

Use EXIT FOR within a loop to exit it early

Graph View

Backlinks