Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Mapping Account Numbers based on a Range

Hi There,

I'm trying to map a range of account numbers to a group.

For example:

Account Groups Exaample:

AccountCodeStartRangeAccountCodeEndRangeAccountType
100000199999Group 1
200000299999Group 2
300000350000Group 3
350001350001Group 4

Account Data

Account Code          Account Name

100001                   Account 1

100005                   Account 2

100010                    Account 3

350001                    Account xxx

etc..

Although the ranges are wide there may only be 5 or 10 accounts in each range and they are frequently changing.

Can anyone think of an efficient way to map the accounts to the account group ranges in the load script?

ps The Account Groups are loaded from a spreadsheet and the Account Data is loaded from a separate database.

Thank you.

1 Solution

Accepted Solutions
Not applicable
Author

You can use IntervalMatch function to map the Account group.

In your example Account codes 100001 will be mapped to Group 1, 100005 will be mapped to Group 1 as well, similarily account code 350001 will be mapped to Group 4.

View solution in original post

2 Replies
Not applicable
Author

You can use IntervalMatch function to map the Account group.

In your example Account codes 100001 will be mapped to Group 1, 100005 will be mapped to Group 1 as well, similarily account code 350001 will be mapped to Group 4.

Not applicable
Author

Brilliant! I was going to build a temporary table and populate every number between the ranges then join them. So glad I asked! Thanks for your help.

For anyone else here is the proof of concept code I used...(Thanks to the great work from John too http://community.qlik.com/thread/31385)

//This is the test data load. The account number here should map into the AccountGrouping Account Number ranges

TestData:

LOAD

          AccountNumber,

          AccountName

FROM

C:\Users\lukec.ADVANCE\Desktop\Integration\IntegrationAccountMapping.xlsx

(ooxml, embedded labels, table is [TestData]);

//This table contains the Account Ranges from the Chart Of Accounts so user can easily group a range of accounts into buckets.

AccountGrouping:

LOAD AccountCodeStartRange,

     AccountCodeEndRange,

     AccountType,

     AccountDefaultSign

FROM

C:\Users\lukec.ADVANCE\Desktop\Integration\IntegrationAccountMapping.xlsx

(ooxml, embedded labels, table is [AccountGrouping]);

//This table contains the list of valid accounttypes

AccountType:

LOAD

          AccountType,

          StatementGroup,

          SortOrder

FROM

C:\Users\lukec.ADVANCE\Desktop\Integration\IntegrationAccountMapping.xlsx

(ooxml, embedded labels, table is [AccountType]);

//The IntervalMatch basically finds account number from the Actual data (AccountNumber) that fits into the AccountGrouping range defined below. e.g. between AccountCodeStartRange and AccountCodeEndRange

//If the account number fit within the range then the tables are joined. N.B. it does not join the entire table. See left join below.

LEFT JOIN (TestData)

INTERVALMATCH (AccountNumber)

LOAD

          AccountCodeStartRange,

          AccountCodeEndRange

RESIDENT AccountGrouping;

//This is the important bit. You have to left join again to the Data Table to get all the additional fields from the Range Table.

LEFT JOIN (TestData)

LOAD *

RESIDENT AccountGrouping;

;

//Not drop the range table so you don't get extra links.

DROP Table AccountGrouping;