topic Re: Mapping Account Numbers based on a Range in QlikView

Mapping Account Numbers based on a Range

Thu, 09 Feb 2012 02:46:18 GMT

Hi There,

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

For example:

Account Groups Exaample:

AccountCodeStartRange	AccountCodeEndRange	AccountType
100000	199999	Group 1
200000	299999	Group 2
300000	350000	Group 3
350001	350001	Group 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.

Re: Mapping Account Numbers based on a Range

Thu, 09 Feb 2012 03:29:36 GMT

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.

Re: Mapping Account Numbers based on a Range

Thu, 09 Feb 2012 05:05:22 GMT

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;