Data Calculator performs lookups at the attribute level. You must enter 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 automatically created and connected. Alternatively, right-click the Data Calculator and select Add Input Port. Connect the OUT-port of the data providing component with the new port. You can add an unlimited number of lookup ports.
Each lookup port must have at least two attributes. The first attribute represents the key. All other attributes represent return values.
To look up compound keys, 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 current option values.
Lookup Name – is inherited from the associated port and cannot be changed here. To change the name of a port, select Description from the port menu.
Lookup Size – to optimize memory allocation and lookup performance, enter the estimated number of lookup records. 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, click the Tabular tab in the Data Calculator window. If lookup ports are available, an additional Lookup column appears.
For each lookup rule, provide:
Key Expression – is the value to search for in the first column of the lookup list. Enter the key expression (for example IN.PR_ID) as a 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. 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 attributes are added or removed.
Output Variable – the lookup return value is assigned to this variable. You can select a variable from the Output Port column (for example, OUT>>OUT.PR_NAME).
(Optional) Default Expression – 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 in the transformation Rules and Current Output Record panel.
