Mapping Specification Lookups
Use a lookup in a mapping specification to look up data in a data object. The Analyst tool queries the lookup source based on the lookup inputs and a lookup condition.
You can use multiple lookups in a mapping specification. After you add a lookup, you can edit it.
You can perform a lookup on the following sources:
- •Flat file
- •Relational table
- •Logical data object
- •Reference table
When you add a lookup to a mapping specification, the lookup returns the result of lookup to the target column. You can configure the lookup to return a single row or multiple rows. You can edit the lookups that you add to a mapping specification to modify how lookups are used in the mapping specification.
You can perform the following tasks with a lookup:
- •Get a related value. Retrieve a value from the lookup based on a value in the source. For example, you can look up an employee ID and return the employee name.
- •Get multiple values. Retrieve multiple rows from a lookup. For example, you can look up a department cost center and return all the employees in a department.
- •Perform a calculation. Retrieve a value from a lookup and use it in a calculation. For example, you can look up a sales tax percentage, calculate a tax, and return the tax to a target column.
Lookup Conditions
When you configure a lookup condition, you compare the value of one or more columns in the source data with values in the lookup.
For example, the source data contains an employee_number. The lookup source table contains employee_ID, first_name, and last_name. You configure the following lookup condition:
employee_ID = employee_number
For each employee_number, the Analyst tool returns the employee_ID, last_name, and first_name column from the lookup.
The Analyst tool can return multiple rows from the lookup that match the lookup condition. You configure the following lookup condition:
employee_ID > employee_number
The Analyst tool returns rows for all employee_ID numbers greater than the source employee number.
Lookup Properties
Configure lookup properties when you add or edit a lookup.
Configure the following lookup properties:
- Name
- Lookup name.
- Description
- Description of the lookup.
- On multiple matches
Determines which rows to return when the lookup finds multiple rows that match the lookup condition. Select one of the following options:
- - Return first row. Returns the first row that matches the lookup condition.
- - Return last row. Returns the last row that matches the lookup condition.
- - Return any row. Returns any row that matches the lookup condition.
- - Return all rows. Returns all rows that match the lookup condition.
- - Report error. The Analyst tool reports an error and does not return a row.
- Lookup Condition
Configure the following properties in a lookup condition:
- - Source Column Name. Name of the source column to compare values with a lookup column .
- - Operator. Comparison operators for the lookup condition.
- - Lookup Column Name. Name of the lookup column to look up values.
- Output
Configure the following output properties:
- - Lookup Column Name. Name of the lookup column to look up values.
- - Target Column Name. Name of the target column to output the results of the lookup.
Adding a Lookup
Add a lookup to a mapping specification to look up data in a data object.
Before you add a lookup to a mapping specification, verify that the data source exists in a project in the Analyst tool.
1. From the Actions menu, click Edit > Lookups.
The Edit Mapping Specification wizard appears.
2. Click the New icon.
The New Lookup wizard appears.
3. Enter a name and an optional description.
4. Click Next.
5. On the Lookup Source panel, select a lookup from a project.
6. Optionally, choose to search for a lookup by name or tag in the search box.
7. Optionally, click Data Preview to preview the lookup data.
8. Optionally, click Properties to view the lookup properties.
9. Click Next.
10. Enter the lookup conditions and properties.
11. Click Finish.
Editing a Lookup
Edit the lookups that you add to a mapping specification to modify how lookups are used in the mapping specification.
1. From the Actions menu, click Edit > Lookups.
The Edit Mapping Specification wizard appears.
2. Select a lookup and click the Edit icon.
The Edit Lookup wizard appears.
3. Enter the lookup conditions and properties.
4. Click Finish.