This is Article 16 of the Qlik Sense Data Modeling Course.
📚 Qlik Sense Course – Article 16 of 28
← Previous Article: Creating a Master Calendar
→ Next Article: Expression Optimization
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.
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 + WHILE! The basic string, math, and date functions that IterNo() works with are covered in the data transformation functions 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 <- Original positions
// ResultRow (RowNo): 1, 2, 3 <- Positions in the result
What are the best practices and common mistakes with IterNo() & AUTOGENERATE?
Mistake 1: Infinite loops
Symptom: Qlik loads endlessly, app freezes.
Wrong:
// DANGEROUS: No upper limit!
LOAD
IterNo() as ID,
Peek('Value') + 1 as Value
AUTOGENERATE 1
WHILE Peek('Value') < 1000000; // What if it's never reached?
Correct:
// SAFE: Max iterations limit
LOAD
IterNo() as ID,
Peek('Value') + 1 as Value
AUTOGENERATE 1
WHILE IterNo() <= 10000 AND Peek('Value') < 1000000; // Stops at 10k at the latest
Mistake 2: Forgetting ORDER BY with Peek()
Symptom: Cumulative calculations are incorrect.
Problem: Peek() accesses the previous row – but in what order?
Wrong:
// Unsorted!
LOAD
Date,
Amount,
RangeSum(Peek('Total'), Amount) as Total
RESIDENT Sales
WHILE IterNo() <= NoOfRows('Sales');
Correct:
// With ORDER BY!
LOAD
Date,
Amount,
RangeSum(Peek('Total'), Amount) as Total
RESIDENT Sales
WHILE IterNo() <= NoOfRows('Sales')
ORDER BY Date ASC; // Important for correct order!
What are the next steps for IterNo() & AUTOGENERATE?
You can now generate sequences and expand records! Next steps:
1. Master Calendar: Practical application of IterNo() for master calendar generation. Creating a Master Calendar shows it in detail.
2. Expressions: Now that you can generate data, optimize the analyses. Expression Optimization is the next step.
What related topics are covered in the IterNo() & AUTOGENERATE course?
- Creating a Master Calendar – Practical application of IterNo()
- Temporal Data – IntervalMatch – Combined with Calendar
Previous: Master Calendar | Next: Expression Optimization