You are here: Processor Library > Audit > Lookup Check

Lookup Check

The Lookup Check processor allows you to check for records in a set of Reference Data that are related to those that you are currently working with, for example, data from another table in a relational database, or related data in a separate system.

Lookup Check uses an exact match (using one or more key attributes) to match records in the Reference Data.

Use

Use Lookup Check when you want to check how many related records for each of your working records exist in a Reference Data table. The Check can fail any records that have either too many, or too few, related records, according to configurable options.

For example, you may want to check that there is at least one Address record for each of your Customer 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 a successful result	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 a successful result	1
Reference Data	Reference Data (See Note below)	Provides access to the 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

None

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
LookupValid	Indicates which data passes the Lookup Check	Y/N

Publication to Dashboard

A Lookup Check's results may be published to the Dashboard.

The following interpretation of results is used by default:

Result	Dashboard Interpretation
Valid	Pass
Invalid	Alert

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 Check 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 Check 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 Check processor produces the following summary statistics:

Statistic	Meaning
Valid Records	The number of records from the working data with an acceptable number of related records in the Reference Data, according to the configured options.
Invalid Records	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 a Lookup Check:

Valid records
Invalid records

Example

In this example, a Lookup Check is performed to check that at least one order (record in the Workorder table) exists for each Customer record. All Customers without any orders may then be tagged as 'Prospects' and not included in the active Customer statistics:

Summary View

Drill-down on Valid Records

Drill-down on Invalid Records