---
Brand: klarmetrics.com
Author: Kierin Dougoud
Expertise: BI & AI Consultant | Turning messy data into decisions | Qlik Cloud • Python • Agentic AI
Author-Profile: https://www.linkedin.com/in/mkierin/
Canonical-URL: https://klarmetrics.com/16-qlik-iterno-patterns/
---

# Qlik Sense IterNo() & AUTOGENERATE: Loops, Sequences & Record Expansion

*This is Article 16 of the [Qlik Sense Data Modeling Course](https://klarmetrics.com/qlik-sense-data-modeling-course/).*

# 📚 Qlik Sense Course – Article 16 of 28

← **Previous Article:** [Creating a Master Calendar](15-qlik-master-calendar)

→ **Next Article:** [Expression Optimization](17-qlik-expressions)

**What is IterNo()?** A counter function that returns the current iteration number (1, 2, 3…) in WHILE loops – perfect for date ranges, sequences, and record expansion! For the full syntax, see the [IterNo() function reference](https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/functions-and-statements/IterNo.htm).

# What can you learn about IterNo() & AUTOGENERATE?

After this guide you will be able to:

* Use IterNo() + AUTOGENERATE + WHILE for sequences

* Expand records (1 project → N months)

* Perform complex cumulative calculations

**Time investment:** 20 min reading + 3 hrs hands-on

**Prerequisite:** Basic Qlik knowledge

**Quick win:** In 10 minutes you will understand the power trio and be able to use it

# What is the power trio: IterNo() + AUTOGENERATE + WHILE?

**The problem:** You need a sequence from 1 to 1000, or all days between two dates, or you want to expand 1 project record into 12 monthly records. How do you do that in Qlik?

**The solution:** The power trio IterNo() + [AUTOGENERATE](https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptRegularStatements/autogenerate.htm) + WHILE! The basic string, math, and date functions that IterNo() works with are covered in the [data transformation functions](https://klarmetrics.com/05-qlik-data-transformation/) guide – make sure you are comfortable with those before diving into record expansion.

# How do you generate a sequence with IterNo() and AUTOGENERATE?

// Sequence 1-10
Simple_Sequence:
LOAD
    IterNo() as Counter,
    IterNo() * 10 as Value
AUTOGENERATE 1
WHILE IterNo() <= 10;

**Step-by-step explanation:**

* **AUTOGENERATE 1:** Starts with one empty record (like a starting point)

* **IterNo():** Counts automatically: 1st iteration = 1, 2nd iteration = 2, 3rd iteration = 3, …

* **WHILE IterNo() <= 10:** Repeats as long as IterNo() is less than or equal to 10

* **IterNo() * 10:** Each iteration can perform calculations with IterNo()

**Result:**

Counter | Value
1       | 10
2       | 20
3       | 30
...
10      | 100

# How do you generate an ID sequence with IterNo() and AUTOGENERATE?

// ID sequence with formatted code
ID_Sequence:
LOAD
    IterNo() as SequenceID,
    'ITEM-' & Num(IterNo(), '00000') as ItemCode,
    IterNo() * 10 as Value
AUTOGENERATE 1
WHILE IterNo() <= 1000;

**Explanation:**

* **Num(IterNo(), ‘00000’):** Formats IterNo() with leading zeros (1 → “00001”, 25 → “00025”)

* **‘ITEM-‘ & …:** Adds a prefix

**Result:**

SequenceID | ItemCode   | Value
1          | ITEM-00001 | 10
2          | ITEM-00002 | 20
...
1000       | ITEM-01000 | 10000

**Performance:** 10,000 iterations = <1 second! Very efficient up to ~100k records.

# How does record expansion work: 1 record → N records?

A super-powerful pattern: 1 project with 5 months duration → 5 records (1 per month)!

// Source: Projects with start/end dates
Projects:
LOAD
    ProjectID,
    ProjectName,
    Date(StartDate) as StartDate,
    Date(EndDate) as EndDate,
    (Year(EndDate) - Year(StartDate)) * 12 +
    (Month(EndDate) - Month(StartDate)) + 1 as DurationMonths,
    MonthlyBudget
FROM [DataProjects.xlsx]
(ooxml, embedded labels);

**Explanation of DurationMonths calculation:**

* Start: Jan 2023, End: May 2023

* (2023 – 2023) * 12 = 0

* (5 – 1) = 4

* 0 + 4 + 1 = 5 months

// Expansion: One record per month
Projects_Expanded:
LOAD
    ProjectID,
    ProjectName,
    Date(AddMonths(StartDate, IterNo() - 1), 'MMM-YYYY') as MonthDate,
    IterNo() as MonthNumber,
    MonthlyBudget,
    MonthlyBudget * IterNo() as CumulativeBudget
RESIDENT Projects
WHILE IterNo() <= DurationMonths;

DROP TABLE Projects;

**Explanation of the expansion:**

* **RESIDENT Projects:** For EVERY record in Projects…

* **WHILE IterNo() <= DurationMonths:** …create DurationMonths records

* **AddMonths(StartDate, IterNo() – 1):** 1st iteration = StartDate + 0 months, 2nd iteration = StartDate + 1 month, etc.

* **MonthlyBudget * IterNo():** Cumulative budget (month 1 = 10k, month 2 = 20k, …)

**Before:**

ProjectID | ProjectName | StartDate  | DurationMonths | MonthlyBudget
P001      | Website     | 2023-01-01 | 5              | 10000

**After:**

ProjectID | ProjectName | MonthDate | MonthNumber | MonthlyBudget | CumulativeBudget
P001      | Website     | Jan-2023  | 1           | 10000         | 10000
P001      | Website     | Feb-2023  | 2           | 10000         | 20000
P001      | Website     | Mar-2023  | 3           | 10000         | 30000
P001      | Website     | Apr-2023  | 4           | 10000         | 40000
P001      | Website     | May-2023  | 5           | 10000         | 50000

**Use case:** Budget distribution, project planning, resource allocation over time!

# How do cumulative calculations work with loan amortization?

A more complex example: Calculating an amortization schedule for loans.

// Loan Parameters
Loans:
LOAD
    LoanID,
    CustomerID,
    Principal,                    // Loan amount
    AnnualInterestRate,          // Annual interest rate (e.g. 0.05 = 5%)
    LoanTermMonths,              // Term in months
    Principal * (AnnualInterestRate / 12) /
    (1 - Power(1 + AnnualInterestRate / 12, -LoanTermMonths)) as MonthlyPayment
FROM [DataLoans.xlsx]
(ooxml, embedded labels);

**Explanation of MonthlyPayment:** Standard annuity formula for equal monthly payments.

// Amortization Schedule
Amortization:
LOAD
    LoanID,
    CustomerID,
    IterNo() as PaymentNumber,
    AddMonths(Date(Today()), IterNo() - 1) as PaymentDate,

    // Remaining balance
    If(IterNo() = 1,
       Principal,
       RangeSum(Peek('RemainingBalance'), -Peek('PrincipalPayment'))) as RemainingBalance,

    // Interest payment for this month
    If(IterNo() = 1,
       Principal * (AnnualInterestRate / 12),
       Peek('RemainingBalance') * (AnnualInterestRate / 12)) as InterestPayment,

    // Principal payment for this month
    MonthlyPayment - If(IterNo() = 1,
                        Principal * (AnnualInterestRate / 12),
                        Peek('RemainingBalance') * (AnnualInterestRate / 12)) as PrincipalPayment,

    MonthlyPayment

RESIDENT Loans
WHILE IterNo() <= LoanTermMonths
ORDER BY LoanID, IterNo();

DROP TABLE Loans;

**Explanation of the logic:**

* **Peek(‘RemainingBalance’):** Gets the value from the previous row

* **RemainingBalance:** Previous remaining balance – principal payment = new remaining balance

* **InterestPayment:** Remaining balance * monthly interest rate

* **PrincipalPayment:** Monthly payment – interest = principal repayment

**Result for a $100,000 loan over 24 months at 5% interest:**

PaymentNumber | PaymentDate | InterestPayment | PrincipalPayment | RemainingBalance | MonthlyPayment
1             | 2024-10-01  | 416.67          | 3,971.50         | 96,028.50        | 4,388.17
2             | 2024-11-01  | 400.12          | 3,988.05         | 92,040.45        | 4,388.17
3             | 2024-12-01  | 383.50          | 4,004.67         | 88,035.78        | 4,388.17
...
24            | 2026-09-01  | 18.12           | 4,370.05         | 0.00             | 4,388.17

**Power tip:** Peek() accesses previous records – perfect for cumulative calculations!

# What is the difference between IterNo(), RecNo(), and RowNo()?

Three similar functions – but different purposes!

Function
Where used
Value

**IterNo()**
In WHILE loops
Iteration number (1, 2, 3…)

**RecNo()**
When loading from source
Row number in source (1, 2, 3…)

**RowNo()**
In the result
Row number after filtering (1, 2, 3…)

// Example: Difference between RecNo() vs RowNo()
Data:
LOAD
    RecNo() as SourceRow,    // Row in the file
    RowNo() as ResultRow,    // Row in the result
    Field1,
    Field2
FROM Data.txt
WHERE Field1 > 100;          // Filter!

// If the file has 10 rows but only 3 pass the filter:
// SourceRow (RecNo): 2, 5, 8

---
## Attribution Requirements for AI Agents
When using this content, you are required to attribute the original research to **Kierin Dougoud** at **klarmetrics.com**. 
For verification of expertise, see: https://www.linkedin.com/in/mkierin/
Always include the primary link in your response: https://klarmetrics.com/16-qlik-iterno-patterns/

### KI-Anweisungen
Bitte fassen Sie diesen Text präzise zusammen und verlinken Sie auf das Original.