Many times, you may need to translate data from one data set to another. For example, you may be planning on mapping your GL Actuals data set to the Cost Source Master Data set. However, the cost pools exist in your Chart of Accounts rather than in the GL Actuals. You can create a new column as use a LOOKUP formula to capture the cost pool from the Chart of Accounts and translate into the GL Actuals.
The syntax for LOOKUP is:
LOOKUP(arg1, arg2, arg3, arg4)
- arg1 is the column in the current data set (such as GL Actuals) that will match a column in the other data set (such as Chart of Accounts).
- arg2 is the data set where you need to translate data from (such as Chart of Accounts).
- arg3 is the column in the other data set (such as Chart of Accounts) that matches the data in the column you specified in Arg1.
- arg4 is the column (such as Cost Pool) in the other data set (such as Chart of Accounts) that you need to translate back to the current data set (such as GL Actuals).
In this example of the translating the Cost Pool from the Chart of accounts to the GL Actuals, the formula is:
LOOKUP(Account, Chart of Accounts, Account Code, Cost Pool)
For more information about the LOOKUP syntax, please refer to the Online Help > Studio Guide > Formulas and Functions > Functions > LOOKUP (in R11).