Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
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;