With the Lookup Heater database fields are dynamically interrogated during the transformation on either the READ or WRITE side. The contents/data of the interrogated database fields can be processed in the Definition any way you like. The Lookup Heater supports all database Adapters. In addition, the Lookup Heater can be used in conjunction with the Excel Adapter, see Excel VLookup configuration.
The Configuration
Lookup Heater - Configuration Execute on InMemory Adapter table: When this option is checked the query is performed on an existing InMemory Adapter table. Note: The table must have been created and filled with values in a previous processing step of the same Definition. As InMemory Adapter tables are processed exclusively in main memory, a signficant speed improvement is attained.
Side: Here you can select from which side of the Definition a database lookup is to be carried out.
Adapter: When several Adapters are available in the Definition you can select here which Adapter (data source) to run the database lookup against. By default the first Adapter available will be used.
Table: Database table or view name that contains the required information.
Field: The table field that the Lookup Heater shall deliver.
Where: The "Where" clause in a Select statement (omitting the word "Where" itself). In this clause the variables $1 ... $x represent the incoming parameters of the Heater.
$1 = first parameter, $2 = second, etc...
OrderBy: Optionally a list of comma separated field names to determine the sort sequence of a database query result that produces more than one record. Note: Only the data from the first applicable record is actually returned
e.g. LastName, FirstName
By default an ascending sorted sequence is assumed. To specify a descending sort sequence instead, it is necessary to add the word DESC after any field(s) to be treated this way.
e.g.LastName DESC, FirstName DESC
In the depicted example (above) the Lookup Heater is configured so that for each record/row on the READ side the FirstName field is obtained from the table t_Address from the database on the WRITE side, with the same ID as the current record on the READ side. Of course, the READ field ID must be connected to the first incoming parameter of the Lookup Heater.
Default value: If you check the option “In cases where no value is available, return this value” you will then be permitted to enter a default value to specify what will result when the database lookup finds nothing.
Excel VLookup configuration
Lookup Heater - Excel VLookup configurationThe Lookup Heater supports use on both the READ and on the WRITE side of the Excel Adapter, in principle it does nothing apart from calling the Excel VLookup function. The input fields for “Where” and “OrderBy” are inactive when the Excel Adapter is selected. For the search criteria = “Where“ the first incoming parameter “$1” is always used and “OrderBy” cannot be used together with the VLookup function.
The syntax described below must be used when specifying an area/range of a particular sheet to perform a lookup within the workbook selected in the Excel Adapter.
The Excel workbook is determined by Adapter selection. To choose a particular Excel sheet this is specified first on the line, followed by an exclamation mark and then the start and end cells of the area/range.
e.g. SheetName!C1:D100
In this case a VLookup will be performed on the sheet labelled “SheetName” in the area/range starting at C1 and ending at D100. Only the first column of the specified area is searched. If the value to be searched for is found, the column index is returned that is specified in the “Field” entry, default = 2. The column index refers to the specified area/range. In the above example this would indicate the value that is found in column D is returned.
Note
- The Lookup Heater supports all (text) input values, including FlowHeater Parameters and environment variables as placeholders.
- Use the SQL Heater instead for more flexible database lookups, including across several tables (via JOIN).
Please also refer to the general information on the use of Heaters (functions)
Example application
- Perhaps you wish to add text data from a CSV file to existing SQL database fields. For example a memo or text commentary field is not to be overwritten, rather extra text added to the end of any existing text in the field. In this case the field would first be elicited using the Lookup Heater and the new text appended using a String Append Heater to the end of the existing text before overwriting the field with the combined text.
- ...
