Wes' Public Vault

Qlik Tutorial - Multiple For Loops

5 min read

WES_0002 Collection

Advanced Data Loading with Nested For Loops in Qlik

note: Hi hello and happy timezone, my name is Wes. Today we will load data using multiple for loops, as in nested for loops, within Qlik.

Introduction

This Lesson covers:

  • Implementing nested For Loops for multi-dimensional data processing
  • Ensuring consistent file names with leading zeroes
  • What to do when a file doesn’t exist? When there is a pattern you expect but cannot find

note: [READ SLIDE] This tutorial assumes you’re already familiar with the basics of Qlik scripting, For Loops, variables, and dollar-sign expansion.

Scenario

LOCAL_FOLDER
--2022_1_Groceries_Sales.csv
--2024_11_Clothing_Sales.csv
--2023_7_Appliance_Sales.csv

How can we load all these files locally?

note: Imagine we have a local folder that has some combination of CSV files, and we wish to load all of this data in Qlik. In this scenario there is an assumption that each file we wish to load in this folder will follow a structured naming convention.

1.1 - Configuration Tables

Years:
LOAD * INLINE [
    Year
    2022
    2023
    2024
];

Months:
LOAD * INLINE [
    Month, MonthName
    1, January
    2, February
    3, March
    4, April
    5, May
    6, June
    7, July
    8, August
    9, September
    10, October
    11, November
    12, December
];

Products:
LOAD * INLINE [
    Product
    Electronics
    Furniture
    Clothing
    Appliances
    Groceries
];

note: Let’s create some simple INLINE tables to match patterns of files that will be stored locally. We can assume that each unique combination from each INLINE table will be a valid file for the sake of this tutorial. We are ALSO going to assume that each file has the same structure with columns and data-types. Instead of hardcoding multiple LOAD statements for every local file, we’ll use nested For Loops to dynamically generate them. This approach is much more maintainable and leads to more abstracted code which I’m always a fan of. [QLIK] Let’s bring these over to Qlik to get ready for the next step NEXT SLIDE . SHOW OFF INLINE TABLES

So let’s approach our solution from scratch so we can understand the step by step. Our table has Year, Month, and Product and we want to use this as a source of truth to dynamically load multiple files with a structured naming pattern. Using the lessons from my previous video, I’ll stick to FieldValueList() as a pattern as it feels very friendly

1.2 - Creating Nested For Loops

// 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
        
    NEXT
    
NEXT

note: This is a pattern that can be re-used with other configuration tables with other apps to help dynamically load a large number of files as long as they follow a consistent pattern, [SLIDES] which is always the dream for a folder of files or a collection of tables on a database

S.1 - Using NUM() for Formatting with Leading Zeros (5:30)

// Examples of using NUM() for zero-padding
LET vMonth = 7;
LET vMonthPadded = NUM($(vMonth), '00');
// Shows: 07
TRACE Month $(vMonth) formatted as: $(vMonthPadded);  

// For larger numbers
LET vProductID = 42;
LET vProductIDPadded = NUM($(vProductID), '000000');
// Shows: 000042
TRACE Product $(vProductID) formatted as: $(vProductIDPadded);

note: What about situations where your files will have leading zeores? Such as the month of May being represented as 05 and not just 5. This is more likely to be the situation in your coding life, and not the 1, 2, 3s that I showed you. While we could have added 0s manually into our table, if there is a chance for someone to mess up manually typing a value, we should assume they miss at-least one. Any amount of consistency we can add to catch small human mistakes like this is preferred. The NUM() function is our hero here. If a particular file name, column value, or anything else needs a consistent padding of leading 0s, we have a tool for this. [QLIK] Let’s show this code

For Leading Zeros, the NUM() function takes two parameters:

  1. The number to format
  2. A format string where ‘0’ indicates a required digit position

note: [SLIDES]

1.3 - What to do when a local file doesn’t exist?

IF IsNull(FileSize('[lib://SalesData/$(vFileName)]')) = -1 THEN
	// File doesn't exist, log it
	TRACE File not found: $(vFileName), skipping...;
ELSE
	// File exists, load it and concatenate
	AllSalesData:
	LOAD
		'$(vYear)' as Year,
		'$(vMonth)' as Month,
		'$(vProduct)' as Product,
		*
	FROM [lib://SalesData/$(vFileName)]
	(txt, codepage is 1252, embedded labels, delimiter is ',');
	
	TRACE Successfully loaded: $(vFileName);
ENDIF;

note: What if there is a consistent pattern of files but not all of them exist? Or maybe they WELL exist at some point but aren’t yet present within the folder? We can create an IF check to look for the existence of a local file and load it into a table, but otherwise just leave a TRACE statement. [QLIK]

Conclusion

  • Using configuration tables to drive dynamic data loading
  • Nested for loops aren’t that scary
  • Formatting numeric values with leading zeros using NUM()
  • Error handling missing files appropriately

note: [SLIDES]. We can use nested for loops along with some silly INLINE tables to help us process a large amount of data in a very readable way. Variables are beautiful and we can embrace abstraction in a very structured way with this example. Thanks for watching my lil tutorial today, I hope for all your loops to work first try after you write them. This presentation was made using Obsidian’s Advanced Slides, and my Obsidian Vault with all my notes will be available in the description

Graph View

Backlinks