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

The following table describes the configuration options:

Configuration	Description
Inputs	Specify 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 options: Minimum number of matches: sets the minimum number of matches in the lookup for data to be returned. 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 data to be returned. Specified as a Number. Default value: 1. Transform if maximum matches exceeded: determines whether or not to return data (the maximum number of matched rows) when the maximum number of matches was exceeded in a lookup. Specified as Yes/No. Default value: No. Lookup reference data: selects the reference 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	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	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). Value is the number of records matched in the set of Reference Data. LookupReturnValue: indicates whether data has been extracted. Value is 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 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 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
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:

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

A summary view:

Transformed Records	Untransformed Records
1718	283

A drill down on the Transformed Records.

CU_NO	LookupCount	LookupReturnValid	Return Value1	Return Value2
13815	1	Y	{13815}	{26107}
15531	2	Y	{15531}{15531}	{26688}{26031}
13861	1	Y	{13861}	{25247}
13870	3	Y	{13870}{13870}{13870}	{26037}{25910}{24857}

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