You are here: Processor Library > Transformation > Lookup and Return

Lookup and Return

The Lookup and Return processor allows you to look up related data in a Reference Data source, and return the data for use in downstream processing.

Where you want to return many related records in the Reference Data, the matching data is returned in array attributes. You may then choose to split this data out (effectively creating a join across the working data and the Reference Data), using Split Records from Array on these array attributes.

Use

Use Lookup and Return to add related data into your process, for example to bring back all the Address records that are related to each Customer record.

Lookup and Return may also be used in a similar way to Lookup Check, to check whether an acceptable number of related records exist in another table or system, but where you want to prove the results of the check by returning some of the matching Reference Data - for example, IDs of the matching records.

Configuration

Inputs

The attributes that you wish to use to lookup against the Reference Data. These should correspond to the attribute(s) that compose the lookup column(s) of the Reference Data.

Options

Option	Type	Purpose	Default Value
Minimum number of matches	Number	Sets the minimum number of matches in the lookup for data to be returned	1
Unlimited maximum matches	Yes/No	Determines whether or not to set a maximum number of matches in the lookup	No
Maximum number of matches	Number	Sets the maximum number of matches in the lookup for data to be returned	1
Transform if maximum matches exceeded	Yes/No	Determines whether or not to return data (the maximum number of matched rows) when the maximum number of matches was exceeded in a lookup.	No
Lookup reference data	Reference Data (See Note below)	Selects the reference data that you wish to look up against.	None

Note:The Reference Data's lookup columns must correspond to the input attributes; that is, there must be the same number of lookup columns as input attributes, and they must be of the same data types.

Outputs

Data attributes

An output attribute is returned for each Return column in the selected Reference Data (and named accordingly). Where the Maximum number of matches option is set to 1 (so for each record, only a single matching record may be returned), the data types of the output attributes will reflect the data types of the Return columns. Where multiple records may be returned, the output attributes will be arrays.

Flags

Flag attribute	Purpose	Possible Values
LookupCount	Stores the count of records matched in the lookup, which may be used in downstream processing (for example, to filter the records using a Value Check)	The number of records matched in the set of Reference Data
LookupReturnValid	Indicates whether data has been extracted	Y/N

Execution

Execution Mode	Supported
Batch	Yes
Real time Monitoring	Yes
Real time Response	Yes

Note: When looking up external data (that is not staged), the appropriate level of performance of the lookup will depend upon there being appropriate indexes on the lookup columns for the selected Reference Data. Also, if looking up external reference data, the Lookup and Return processor will always appear with a re-run marker, indicating that it will be completely re-executed each time the process is run, regardless of whether or not the actual processor configuration has changed. This will also mean that processors that are downstream of the Lookup and Return processor will need to be rerun. This is because OEDQ cannot detect whether or not the external reference data has changed, and therefore has to assume that it has changed (since external lookups are usually used for dynamically changing reference data), and so re-executes the lookup in order to ensure the consistency of dependent results.

Results Browsing

The Lookup and Return processor produces the following summary statistics:

Statistic

Meaning

Transformed Data

The number of records where data was returned.

This is the number of records from the working data with an acceptable number of related records in the Reference Data, according to the configured options.

Untransformed Data

The number of records where data was not returned.

This is the number of records from the working data with an unacceptable number of related records in the Reference Data, according to the configured options.

Output Filters

The following output filters are available from the Lookup and Return processor:

Transformed records
Untransformed records

Example

In this example, the Lookup and Return processor is used to look up order records (from a Workorder table) that are related to Customer records, using the CU_ID attribute as a lookup key, and returning enough information from the Workorder table to be able to identify each order. Data is returned provided at least one order record matches.

Summary View

Drilldown on Transformed Records

Note that in many cases above, many records were found matching the lookup column, so the data is returned in array attributes.