1.3.2.10 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 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.

The following table describes the configuration options:

Configuration	Description
Inputs	Specify the attributes that you want 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	Specify the following valid value options: Minimum number of matches: sets the minimum number of matches in the lookup for a successful result. Specified as a Number. Default value: 1. Unlimited maximum matches?: determines whether or not to set a maximum number of matches in the lookup. Specified as Yes/No. Default value: No. Maximum number of matches: sets the maximum number of matches in the lookup for a successful result. Specified as a Number. Default value: 1. Reference Data: provides access to the data that you want to look up against. Specified as Reference Data. 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. Default value: None.
Outputs	Describes any data attribute or flag attribute outputs.
Data Attributes	None.
Flags	The following flags are output: 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). Possible values are the number of records matched in the set of Reference Data. LookupValid: indicates which data passes the Lookup Check. Possible values are Y/N.

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 EDQ 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.

The following table describes the statistics produced by the profiler:

Statistic	Description
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:

• 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:

A summary view:

Valid Records	Invalid Records
1718	292

A drill down on invalid records:

CU_NO	CU_NO.count.1
13810	0
13833	0
13840	0
13841	0
13865	0
13877	0
13938	0
13950	0
13952	0
13966	0
13971	0
13977	0
14001	0