Data Calculator performs lookups on the attribute level. You must enter the lookup data at special lookup ports.
To add a lookup port to Data Calculator, connect the OUT-port of the data providing component directly to the Data Calculator component (not a port). A lookup port is created and connected automatically. Alternatively, right-click the Data Calculator and select Add Input Port. Connect the OUT-port of the data providing component with the new port. The number of lookup ports is unlimited.
Each lookup port must have at least two attributes. The first attribute represents the key. All other attributes represent return values.
If you want to look up compound keys, you must concatenate the key values within a preceding component and use the same kind of concatenation on the key expression for the lookup.
Use the Lookup Options property to configure the lookup. The Properties window displays a list of all lookup ports and its current option values.
Lookup Name — is inherited from the associated port and cannot be changed here. To change a name of a port, select Description from the port menu.
Lookup Size — enter the estimated number of lookup records to optimize memory allocation and lookup performance. See “DB Lookup”.
Lookup Empty / Null — empty and null are normally handled as “unknown” keys, thus returning the lookup default value. If empty or null values are valid keys for your lookup, you can enforce looking up the values for these keys by activating this option.
To set up Lookup rules open the Tabular tab in the Data Calculator window. If Lookup ports are available, an additional Lookup column appears.
Provide the following information for each lookup rule:
Key Expression – This is the value to search for in the first column of the lookup list. You enter the key expression (for example IN.PR_ID) as the Transformation Rule.
Return Value – Since lookup lists can have more than one return value column, you must specify which value to return, if the key is found. To specify the value to return, select the associated port attribute from the pop-up menu on the Lookup column. For example: LOOKUP1>>LOOKUP1.PR_NAME.
Although return values are selected and displayed by
name, the lookup internally uses the column number. This means you
must review your lookup rules whenever the lookup port structure
is modified, especially after adding or removing attributes.
Output Variable – the Lookup return value is assigned to this variable. You can select a variable from the pop-up menu on the Output Port column (for example, OUT>>OUT.PR_NAME).
Default Expression (optional) – a lookup returns Null if the given key value is not found. To return a different default value, enter an expression in the Lookup Options window. To open the Lookup Option window, click the icon in the Lookup column, under the transformation Rules and Current Output Record panel. The Lookup Options window is used to specify a default expression. It includes popup menus that allow you to select Lookup and Output Attributes.
