topic Re: Use String Function Keepchar and Intervalmatch in QlikView

Use String Function Keepchar and Intervalmatch

Anonymous — Mon, 26 Jan 2026 18:19:17 GMT

So, we have a problem with Numbers in Excel file, (bankaccount.xls) that we need to use in Qlikview

Bankaaccount has four columns, TypeOfAccount, Description, TotIncome, BankNumber as this.

TypeOfAccount

Description

TotIncome

BankNumber

Summary

Sum of All

50000

B1000-3000;B3000-3500

Loan

Loan account

600000

B2000-3000;B4000-4100

The important column, BankNumber looks like this: B1000-3000;3500-3700

For the task we need to create a row for each bank number, e.x. 1000, 1001, 1002, 3501 etc.
So at the start it looks like this. We use the String Function - Purgechar to remove the B.

BankAccount:

LOAD

TypeOfAccount,

Description,

TotIncome,

BankNumber , PurgeChar ( BankNumber, 'B' ) as NewBankNumber

FROM

[..\Desktop\Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

Resulting in 1000-3000;3500-3700.

Then we use SubField to separate the start and end account numbers, inside a resident load.

BankAccountNew:

LOAD

NewBankNumber,

SubField(NewBankNumber, ';',1) as FirstAccount,

SubField(NewBankNumber, ';',-1) as SecondAccount

Resident

BankAccount;

Resulting in A separate table "BankAccountNew" that holds the start and end account number

Start	End
1000	2000
2000	4100

I believe we need to use Intervalmatch to create each bank account 1000,1001,1002 etc.. but here I not sure how to write the Intervalmatch function.

Can anyone help me?

‌

Re: Use String Function Keepchar and Intervalmatch

antoniotiman — Fri, 24 Nov 2017 18:00:33 GMT

May be this

Temp:
LOAD TypeOfAccount, Description, TotIncome,SubField(PurgeChar(BankNumber,'B'),';') as BankNumber Inline [
TypeOfAccount,Description, TotIncome,BankNumber
Summary,Sum of All,50000,B1000-3000;B3000-3500
Loan,Loan account,600000,B2000-3000;B4000-4100];
NoConcatenate LOAD TypeOfAccount, Description, TotIncome,
SubField(BankNumber,'-',1)+IterNo()-1 as BankNumber
Resident Temp
While SubField(BankNumber,'-',1)+IterNo()-1 <= SubField(BankNumber,'-',2);
Drop Table Temp;

Regards,

Antonio

Re: Use String Function Keepchar and Intervalmatch

Anonymous — Sat, 25 Nov 2017 09:19:39 GMT

Thanks for replying Antonio

From your teachings,I learned abit about using functions in the same table. And how the IterNo()-1 and While works.

Thanks lovely,

And I see my version of Qlikview (PE) can only handle a maximum amount of numbers..
Meaning, removing one 0 from each value .. 1000 becomes 100, 3000 becomes 300, 3500 becomes 350 etc.
Then all values are showing.

Thanks for the solution