Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Use String Function Keepchar and Intervalmatch

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
DescriptionTotIncomeBankNumber
 Summary
Sum of All50000
 B1000-3000;B3000-3500
 Loan
Loan account600000
 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:

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:

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

StartEnd
10002000
20004100

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?

1 Solution

Accepted Solutions
Master III

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]
;
SubField(BankNumber,'-',1)+IterNo()-1 as BankNumber
Resident Temp
While SubField(BankNumber,'-',1)+IterNo()-1 <= SubField(BankNumber,'-',2);
Drop Table
Temp;

Regards,

Antonio

2 Replies
Master III

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]
;
SubField(BankNumber,'-',1)+IterNo()-1 as BankNumber
Resident Temp
While SubField(BankNumber,'-',1)+IterNo()-1 <= SubField(BankNumber,'-',2);
Drop Table
Temp;

Regards,

Antonio

Anonymous
Not applicable
Author