A reference data table can be used to look-up values that you want to use as substitutes for the existing values present in a data set. For example, where one data set contained company names such as "Circle Traders Ltd", and the corresponding values in the other data set used codes such as "XY123", you could use look-ups to achieve successful matches.
Sometimes both a look-up table and a text equivalence rule can achieve the same results, but in certain scenarios value substitution is necessary.
Consider the following example:
Party code → Party name translation table
*You can download the example data used in this example at the bottom of this article.
The two data sets contain equivalent data about payments. We can note that:
- In data set 1, multiple party codes can be used to identify the same seller. E.g.: Both AB001 and XY123 identify Circle Traders Ltd
- The records in data set 1 need to be rolled-up to be matched with the records in data set 2.
GENERAL NOTE:
The look-up key (composed of one or many reference data fields) in the reference data table has to be unique. If the system finds more than one target record that matches NRL conditions it will not know which one to pick and the NRL rule will return the value or column set up in the 'otherwise' clause. Considering the example above - if Code AB027 occurs twice the system will not return Party name in the match field.
Process set-up
Set up the process as usual:
- Set up the data inputs
- Set up the match fields as usual except the "receiver" field.
Table set-up
- Go to the Reference data screen and select Create table
- Enter the table name, and optionally a description
- Click on Create table
- Upload the table data
- Associate the table to the process "Payments"
The table is now available in the "Payment" process.
Click on Exit settings to see the values in the table.
Look up a value from the reference table in a match rule
Go back to the match fields in the "Payments" process.
Set receiver as a match field.
Create the following transformation rule:
- Chose "a value for reference data table" (you can type "table" to see a list of rules about reference data tables)
Chose the table "Party codes"
Duco will then show the following:
- Use the menu system to construct a rule as follows:
Results
The results should look as follows:
If we add in a Reported field for the original value for "receiver", we can see at the far right showing the original value "AB027", as well as the "receiver" field used for matching showing the value "Solvent Securities Ltd", which is the value that results from the table look-up.
Add a roll-up rule
To complete this particular example you'll need to also add a roll-up rule.
Construct a roll-up rule as follows:
