The Cross Reference table is a data structure used to replace a runtime lookup computation with a much simpler lookup operation. The Cross Reference Table is easy to use and requires no coding. The processing speed gain can be significant, because retrieving a value from memory is much faster than performing a database or other Connector lookup.
The Cross Reference table is comprised of a set of data elements (or values) that are organized using a model of horizontal rows and vertical columns.
Some common uses:
- Simple value translation between System A and System B, such as item codes, unit of measures, status codes, or any other type of code
- Reusable translations (for example, US State abbreviations)
- Switch/case logic (simple if/else)
- Atom-specific Map default values
- Atom-specific Connection default values (Start Shape criteria)
- Parameterize any Process step with Atom-specific values for deployment to multiple locations or customers
How It Works
One or more column(s) in the table can be specified as the input column(s). The input column(s) contains the Input Data Element(s). Only one column can be designated as the output column. The Output column contains the Output Data Element.
You can assign one or more Input Values(s) to be compared to one or more Input Data Element(s) in the table. The Input Values can come from the current data, date/time, profile elements, sequential values, static values, tracked properties, and/or unique values.
The comparison is performed within each row. One by one, the Input Values are compared to their assigned Input Data Elements. If each Input Value matches its assigned Input Data Element in the row, the corresponding Output Data Element in that row is returned. If there is no match the Cross Reference table will return a null value.
 | If you're familiar with writing SQL queries, you can think of the Cross Reference Table functionality like a simple SELECT query with one or more columns returned and one or more WHERE clauses criteria:
SELECT Reference 2 FROM Cross_Reference_Table WHERE Reference 1 = <Input Value> |
Configuration
To create a new Cross Reference Table:
- Click
New in the Component Explorer of the Build Tab
- Choose Cross Reference Table as the Type
- Enter a name for the Cross Reference Table
- Choose the folder in which to save the Cross Reference Table
- Click OK
To enter data into the table, click the Add (
) button to add a new row and type in the values for each column. Click or use the Tab key on your keyboard to move between columns. You can add an unlimited number of rows and use up to six columns and you do not need to use all the columns.
Note: Currently values cannot be imported from a file.
The icons above the columns allow you to:
- Add a row after the last row in the table
Create an exact copy of any selected rows
Delete any selected rows
Select all rows in the table
Deselect all rows in the table
Rename column headers
Enable Atom-specific values. This option allows you to specify references for specific Atoms. Once this is enabled, it cannot be undone.
Cross Reference Table look ups are most often performed in Map Functions (under Lookup category) but can also be used as parameter values in all Process steps that use parameter configuration such as Connectors (including the Start Shape), Decisions, Document Properties, Messages, Program Commands, and Exceptions.
Examples
The Cross Reference Table shown below is an example of reusable translations. The information contained in the table pertains to the US State abbreviations and other state codes.
One Input Value and One Output Value
The Cross Reference Table can be used to accept one input value and produce one output value. The example below shows a Cross Reference Table lookup within a function. In addition to setting up the function, you would need to map all input elements from the source profile to the input values in the function. You will also need to map all output values from the function to the output elements in the destination profile.
Example Scenario
- System A uses the State values to refer to the states.
- System B uses the State abbreviations.
When mapping from System A to System B, you will need to translate the value in State to the State Abbreviation value. The SQL Select statement for the Output_Element would be 'select State_Abbreviation from Cross_Reference_Table where State = Input_Element'. If the State=Alabama in System A then the State Abbreviation='AL' for System B. 'AL' is the value that would be returned in the output.
More than One Input Value and More than One Output Value
The Cross Reference Table can be used to accept more than one input value and produce more than one output value. The example below shows a Cross Reference Table lookup within a function. In addition to setting up the function, you would need to map all input elements from the source profile to the input values in the function. You will also need to map all output values from the function to the output elements in the destination profile.
Example Scenario
- System A uses the State values and the Code values to refer to the states.
- System B uses the State Abbreviation values and the FIPS State Code to refer to the states.
When mapping from System A to System B, you will need to translate the value in State to the State Abbreviation value. The SQL Select statement for the Output_Element would be 'select State_Abbreviation from Cross_Reference_Table where State = Input_Element'. If the State=Alabama in System A then the State Abbreviation='AL' for System B. 'AL' is the value that would be returned in the output.
When mapping from System A to System B, you will also need to translate the value in Code to the FIPS State Code value. The SQL Select statement for the Output_Element would be 'select FIPS State Code from Cross_Reference_Table where Code = Input_Element'. If the Code=ALAB in System A then the FIPS State Code='01' for System B. '01' is the value that would be returned in the output.
Advanced Configuration: Atom Specific values vs. Default values
This feature can only be used if your account has been set up to allow for more than one Atom. It is an Advanced feature that cannot be reversed. The Atom Specific Cross Reference Table shown below allows you to specify which values are generated based on the Atom that is running the process. If an Atom does not need a specific configuration, you can use the Default value in the Atom column. You can select the Atom by clicking the Choose Atom icon. The information contained in the table pertains to the US State abbreviations and other state codes.
Example Scenario
- System A uses the State values to refer to the states.
- System B uses the State Abbreviation values to refer to the states.
When mapping from System A to System B, you will need to translate the value in State to the State Abbreviation value. The SQL Select statement for the Atom Specific Output_Element would be 'select State_Abbreviation from Cross_Reference_Table where State = Input_Element and Atom=<Atom Name> Atom=<Default Atom>.
If the State=Alabama in System A and the Atom=FinalAtom then the State Abbreviation='AL' for System B. 'AL' is the value that would be returned in the output.
If the State=Alabama in System A and the Atom=<Default Atom> then the State Abbreviation='ALA' for System B. 'ALA' is the value that would be returned in the output.
This page
Other pages
Account
