Transform functions – Internal lookup

What are lookups used for?

When data is imported from one system into another, the source data has to be compatible with the destination system.
Where two systems are being integrated, a lookup may be used to provide translation so that they are compatible.

An example of a lookup

pic1As an example of the translation function of lookups, the data being processed may include country codes. The source data system may refer to the United Kingdom as “United Kingdom” the destination system as “UK”.

The lookup function will use the reference to apply the correct codes, translating all references to “United Kingdom” as “UK”. The lookup ensures that the source and destination systems are compatible.

External or internal?

Two types of lookups are provided by the data transformation component, external and internal.

External LookupsInternal Lookups
Large tables of data with many thousands of rowsSmall tables with relatively low numbers of records
Contain dynamic data subject to changeTend to contain stable, static data
Any database with an ODBC or OLE DB driver, where TaskCentre has connectivity and access to the databaseStored locally on the TaskCentre server, where content may be added by the task designer

Focus on internal lookup

The Internal Lookup function allows an internal lookup table to be used when replacing the values from one system with different values in another system.

Internal lookup tables are stored locally on the TaskCentre server. Their data content can be added by the task designer.

Using the internal lookup function

Let’s take a look at a situation where an internal lookup function could be used.

An organisation is downloading orders from a web site. They wish to translate codes between the source system, the web site, and their destination system.

In the source system, the Status item of a sales order has the value “Pending”. In the destination system, the equivalent Status item is given the value “Open”.

So they use an internal lookup to provide the translation between the source and destination systems.

pic2

Above: You can see the DTC (for the Web Service Connector tool), showing a data transformation mapping for the scenario outlined.

pic3

Above: The internal lookup function is being applied to a transformation mapping between the data input Status element and tool input Status element.

pic4a In place of the Transformation Mappings pane, you can open the function’s settings in this pane by double-clicking the function icon.

pic5

Note that the pane is now titled Transformation Function. After using the Transformation Mappings pane to drag functions and connectors into position between the data and tool input panes, you can switch to this view and use the Transformation Function pane to specify various transformation settings.

pic6

pic7

Above: In the tables under the Input and Output tabs, you can view the input and output parameters that have been mapped in the Transformation Mappings pane, together with their mappings.

pic8

Above: The settings on the Options page allow you to specify what action should take place in the event of a missing input parameter.

The default method of handling these exceptions is to output no data.

In our example, the default setting has been changed so that, if the input parameter is missing, it will output the value “Open”.

This is a more suitable behaviour for this sort of scenario where new orders are being downloaded from a web site.

pic9

Above: On the Table tabbed page, data can be entered manually into a grid.

Values are specified for each of the selected input and output parameters. Notice here that “Pending” in the source (input data) becomes “Open” in the tool input (output data).

Internal lookup functions: In summary

The lookup function is used to provide a reference against which to check and translate input data, to ensure that it is compatible with a destination system.

TaskCentre provides two types, internal and external. Internal lookups are suitable for static data and low-volume records. External lookups are better for dynamic data and large data records.

The Internal Lookup function allows an internal lookup table to be used to replace the values from one system with a different set of values when processing into another system. Internal lookups can be stored locally on the TaskCentre server. Their data content can be added by the task designer.

To apply an internal lookup, you use the DTC mappings pane to drag connections between data input and tool input parameters. You can also open the lookup function’s settings dialogue and specify lookup settings. These include input and output parameters and their mappings, exception behaviour and table data.

Links to related DTC articles