Use the LOOKUP function to look up values in a source module or list and display the values in a target module using mapping criteria. These examples demonstrate how you can use LOOKUP in different scenarios in your models.
This example shows how to look up salaries from a list, Grades, and populate the Employee Salaries module with salaries.
The Grades list has a property called Salary, which is formatted as Number. The Grades list looks like this in Grid View:
Parent | Code | Salary | |
Grade 1 | 40,000 | ||
Grade 2 | 45,000 | ||
Grade 3 | 50,000 | ||
Grade 4 | 60,000 |
The Employee Salaries module has the Employees list on the rows and two line items on the columns called:
Grades.Salary[LOOKUP: Grade]
In the Employee Salary module, when you select the grade for an employee, the Salary field is populated with the corresponding value from the Grades list.
Say you want to look up the salary for employees. You have a Pay table module based on grade and region and you want to display the salary in an Employee salaries results module.
The mapping between source and results modules is done on Region and Grade.
Region and Grade are dimensions in the source module, Pay table. They're also list data type line items in the results module, Employee salaries.
For the results module, LOOKUP uses the mappings for Grade and Region to return employee salaries from the source module Pay Table, and the line item Basic Pay.
The formula used to get the figures in the Salary column is:
Pay Table.Basic Pay[LOOKUP: Grade, LOOKUP: Region]
The source module, Pay Table, has a line item on pages, Basic Pay, which has a number data type. It has the Region list on columns, and the Grades list on rows.
This table shows the data for the Basic Pay line item:
Region A | Region B | Region C | Region D | |
Grade 1 | 20,000 | 21,000 | 25,000 | 33,000 |
Grade 2 | 30,000 | 31,000 | 35,000 | 38,000 |
Grade 3 | 40,000 | 41,000 | 45,000 | 49,000 |
The Employee salaries module has the Employees list on rows, and the following line items on the columns:
Pay Table.Basic Pay[LOOKUP: Grade, LOOKUP: Region].
The table shows the Employee salaries module. When the Grade and Region is selected for each employee, the Salary value is populated for the employee by the LOOKUP formula.
Employees | Grade | Region | Salary |
Employee x | Grade 2 | Region B | 31,000 |
Employee y | Grade 3 | Region C | 45,000 |
Employee z | Grade 3 | Region A | 40,000 |
This example shows you how you can use LOOKUP on a time period. You have a source module, Pay region, and a results module, Staff costs. You look up the staff costs in the Pay region module, and map the costs by Region and Period.
You can use LOOKUP when the source line item is a finer timescale than the mapping line item data type. Similarly you can use LOOKUP when the dimension of the target line item is a finer timescale than the dimension of the mapping line item.
However, if the time scale that you use in the results does not exist in the source, then LOOKUP returns 0. For example if you select Half-year totals in the results but it is not selected in the source, LOOKUP returns 0.
The source model, Pay region, has a line item on pages, Pay, which is formatted as a number. It has the Regions list on columns, and the Time dimension on rows.
This table shows the data for the Pay line item:
Jan | Feb | Mar | |
Americas | 160,667 | 166,500 | 176,250 |
Europe | 166,833 | 166,833 | 166,833 |
Asia Pacific | 129,333 | 137,000 | 143,917 |
All Regions | 456,833 | 470,333 | 487,000 |
The results module, Staff costs, has the following line items on columns:
Pay region.Pay[LOOKUP: Regions, LOOKUP: Period]
The Transactions list is on rows.
This table shows the Staff costs module.
Regions | Period | Costs | |
Transaction 01 | Europe | Jan | 166,833 |
Transaction 02 | Americas | Jan | 160,667 |
Transaction 03 | Asia Pacific | Jan | 129,333 |
You can see that the values in the Costs column come from the Pay region source table. For example, the Transaction 01 value 166,833 comes from the cell at the intersection of Europe and Jan.
Disclaimer
We update Anapedia content regularly to provide the most up-to-date instructions.