Connecting 2 tables using a mapping table

[#1]

3 tables: table A, B and C.

In table A old accounts, table B the translation from old to new accounts, table C new accounts.

Table A: Account, Amount, Year, Period (data for years 2008, 2009, 2010)

Table B: Old Account, New Account

Table C: New Account, Amount, Year, Period (data for year 2011, 2012, etc.)

Overview

New Account 1, Amount, Year, Period

New Account 2, Amount, Year, Period

New Account 1, Amount, Year, Period

How do I setup the report/tables that they only reflect the new accounts (where some really are new accounts and some are translations of old accounts).

Any help is appreciated!

[Miguel_Angel_Baeyens · Accepted solution]

Hello,

I understand that you can load old accounts values into new ones, so you are going to report on 2008, 2009, 2011, etc..

Then, load Table B something like

AccountsOldNewMap:MAPPING LOAD OldAccount, NewAccount FROM Source;

Then, load Table C with something like

Table C:LOAD ApplyMap('AccountsOldNewMap', OldAccount) AS NewAccount, Amount, Year, Period FROM Table A

mapping old accounts into new accounts and getting the results you are looking for. Hope this is what you are expecting.

[#3]

Thank you very much for your help.

This is the statement now in Table B:

AccountsOldNewMap:

MAPPING LOAD mid(Mfgvalue,6,6) AS OldAccount, mid([Oracle value],8,7) AS NewAccount

FROM (ansi, txt, delimiter is ',', embedded labels, msq)

This is the statement now in Table C:

NewMap:

LOAD ApplyMap('AccountsOldNewMap', OldAccount) AS NewAccount

FROM

(qvd)

This is the error message:

Field not found - <OldAccount>

What am I doing wrong?

[Miguel_Angel_Baeyens]

Hi,

Following your script above, you are using

mid(Mfgvalue,6,6)

as the old account number. In BS_Details.qvd file you have to have a old account number you want to change for its correspondent in the Map table. That is the value you have to use as paramenter in ApplyMap. Does that Mfgvalue exist in that BS_Details.qvd file?

[#5]

It does now.

However I now get some of the old accounts (6 positions) and some of the new accounts (7 positions).

Have looked everywhere, don't have a clue.....

[Miguel_Angel_Baeyens]

Hi,

ApplyMap uses the table returning one value depending on the expression passed on to the map. If there are no matches, you can even create a default map which will match any of the values you don't want to work with (as they don't have correspondence in the mapping table) and you may use this to load a third table clean of any records but those who actually exists in the mapping table, which are supposed to be in your reports (as they are the new accounts). Changing the code for

ApplyMap('AccountsOldNewMap', OldAccount, 'Whatever String Otherwise') AS NewAccount, OldAccount as OriginalAccount,

you can easily group all "Whatever String Otherwise" meaning those old accounts not existing in your mapping table.

Now you can forget them (set analysis, conditionals), or check your map table should each and any of your old accounts have a new name (and having that new name is always different).

[#7]

I'll check this during the work.

Thank you for your help.