Wes' Public Vault

Qlik Tutorial - For Loop

6 min read

See Qlik Tutorial - For Loop - Collection for related notes

Advanced For Loop Techniques in Qlik’s Data Load Editor

note: Hi hello and happy timezone, my name is Wes. This tutorial is on advanced For Loop techniques in Qlik’s Data Load Editor.

Introduction

This Lesson covers:

  • For Loops
    • Using variables effectively within For Loops
    • How to iterate over INLINE tables with loops
    • A better way to loop over INLINE tables
  • Side-Notes
    • Dollar-Sign-Expansion i.e. $()
    • SET / LET (Variables)

note: READ SLIDE. This tutorial assumes you have basic familiarity with Qlik and its scripting language. Let’s hop in.

1.1 - For Loop Tracing

// Basic For Loop with TRACE statements
FOR vLoop = 1 TO 5

    TRACE vLoop: $(vLoop);
    
NEXT

note: (2:00) I wanna’ start with a simple For Loop example that demonstrates how to use TRACE for debugging. SWITCH. NEXT

TRACE statements appear in the script execution progress window, they can help you monitor the execution flow and inspect variable values during runtime. TRACE statements are your best friend when you’re unsure why something isn’t working as expected. You can think of it as Qlik’s equivalent of print statements, logging statements, etc. in other programming languages.

S.1.1 - SIDE-NOTE

Dollar-Sign Expansion in Qlik

$(variable_name)


// Without dollar-sign expansion
FOR vLoop = 1 TO 3

	// This looks for a file literally named "Table_vLoop.csv"
    TRACE LOAD * FROM [Table_vLoop.csv]; 

NEXT

note: Side-note about Dollar-Sign-Expansion. SWITCH. It allows you to reference the content of a variable rather than the variable name itself. It’s done by surrounding a variable with parentheses and putting a dollar-sign in front. This first example here won’t result in any dynamic output, we can step over to Qlik and run this. SWITCH. RUN CODE

S.1.2 - SIDE-NOTE

Dollar-Sign Expansion in Qlik

$(variable_name)

// With dollar-sign expansion
FOR vLoop = 1 TO 3

	// This looks for files named 
	// "Table_1.csv", "Table_2.csv", etc.
    TRACE LOAD * FROM [Table_$(vLoop).csv]; 

NEXT

note: Remember that dollar-sign expansion happens at script runtime before the statement is executed. Even Qlik doesn’t know the value of the variable until runtime. SHOWCASE DEUBG MODE

1.2 - For Loop Using 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

note: SWITCH. For Loops can be made even more flexible by using variables to control their behavior. This example starts at year 2020 and iterates to year 2023, stepping 1 year at a time. Variables make your scripts more maintainable and adaptable. SWITCH. RUN CODE.

S.2 - SET vs LET Commands in Qlik

SET Command:

  • Assigns the literal text string to the variable
  • Does not evaluate expressions or perform dollar-sign expansion
  • → Useful when you want to store formulas or expressions as text

LET Command:

  • Evaluates the expression and assigns the resulting value to the variable
  • Performs calculation and dollar-sign expansion
  • → Generally preferred in scripting for dynamic values

note: When working with variables in loops, it’s crucial to understand the difference between SET and LET commands in Qlik. SWITCH. READ SLIDE.

// Using SET (literal)
FOR vLoop = 1 TO 3

    SET vValue = 'vLoop * 10';
    // Shows 'vLoop * 10' each time, not evaluated
    TRACE SET result: $(vValue); 

NEXT

// Using LET (evaluated)
FOR vLoop = 1 TO 3

    LET vValue = vLoop * 10;
    // Shows 10, 20, 30
    TRACE LET result: $(vValue); 

NEXT

note: Let’s see how they behave differently in a loop. Understanding this distinction helps prevent common errors in your Qlik scripts, especially when working with loops and dynamic expressions. SWITCH. RUN CODE.

2.1 - Looping Through INLINE Tables


// Create an INLINE table of parameters
RainbowColors:
LOAD * INLINE [
Color
Red
Orange
Yellow
Green
Blue
Indigo
Violet
];

note: SWITCH. Qlik INLINE Tables are a quick and easy way to store data and process it. Let’s bring this on over into Qlik. SWITCH

// Loop through the INLINE table
FOR vLoop = 0 TO NoOfRows('RainbowColors') - 1

    LET vColor = Peek('Color', $(vLoop), 'RainbowColors');
    TRACE Color: $(vColor);

NEXT

note: Utilizing NoOfRows() allows us to count the number of rows of the table and smoothly iterate through it. SWITCH.

SHOW DOCS of NoOfRows()

Peek() can also be used to help with INLINE table iteration

SHOW DOCS of Peek, this is why we start at 0 and not 1

RUN CODE

note: While this code worked well enough for our purposes, it required multiple methods and multiple variables to keep of information. If only there was a better way, oh wait! SWITCH There is!

2.2 - For Loop with FieldValueList

// First, create a simple INLINE table
Products:
LOAD * INLINE [
ProductID, Category
101, Electronics
102, Furniture
103, Electronics
104, Clothing
105, Clothing
];

FOR EACH vCategory IN FieldValueList('Category')
    TRACE Processing category: $(vCategory);

	// Processing Code
    
NEXT

note: FieldValueList() is a method in Qlik that can reference an existing Field and get a list of its values for quick and easy iteration. SWITCH

SHOW DOCS. RUN CODE.

This “For Each” pattern is much cleaner than the traditional approach we have been using. Notice how we don’t need to:

  • Count the number of values
  • Keep track of an index variable

The For Each loop automatically iterates through each unique value found in the Category field, making your script more readable and less prone to errors.

Conclusion

  • For loops are cool
  • TRACE statements help find mistakes
  • LET yourself have a good day and don’t be SET in your ways

note: SWITCH. Thanks for watching, I hope you learned and had fun. This was made using Obsidian Advanced Slides and my related Obsidian Vault will be linked in the description

Graph View

Backlinks