2 Replies Latest reply: Feb 9, 2012 12:05 AM by Niels van Diermen RSS

    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.

        • Re: Mapping Account Numbers based on a Range

          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

              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;