Using Translation tables (Value Lookup)
In the previous chapter we configured a shuffle on the last_name and last_name_upper columns and checked the translation table option. This table is not yet created within our database, but will be when we execute this template. In the Gasware Database there is also a CUSTOMER_HISTORY table containing last names that must be masked consistently with the CUSTOMER table.
We can do that with the Value Lookup function in combination with the translation table. The value lookup function works almost the same like the Random Lookup. However it does not pick a random value from the lookup table, but uses a current value to pick the right value from the lookup table.
For example
Original Table
| CUS_ID | FNAME | LNAME |
|---|---|---|
| 1 | John | Doe |
| 2 | Mary | Watson |
| 3 | Chris | Johnson |
Lookup Table
| LNAME_OLD | LNAME_NEW |
|---|---|
| Doe | Watson |
| Watson | Johnson |
| Johnson | Doe |
If you want to mask the LNAME with the masking translation in the Lookup Table you will use as input the LNAME matching on LNAME_OLD and as output replacing LNAME with LNAME_NEW.
Adding a value lookup
Now let's add a value lookup on the last names in customer history!
- Select the CUSTOMER_HISTORY table
- Right click the LAST_NAME column and click Add function → Value lookup...
The function editor will open to configure the value lookup function. This function is a bit more complex that the previous ones. It consists of an input and output mapping. Think of the input mapping as joining the table to be masked with the lookup table. The output mapping is used to determine which columns of the table must get a new value from the lookup table. This means that you can also replace multiple column values at once when your lookup table contains all the new values.
When an input value could not be found within the lookup table an alternative masking strategy can be applied with the If not found then option.
- Select the TT_LASTNAMES table from the Lookup table drop down list (translation tables can be selected within drop down lists instead of typing them in manually)
- In the Input mapping: Double click the empty Input column and choose LAST_NAME from the drop down list
- In the Input mapping: Double click the empty Lookup column and choose "LAST_NAME" from the drop down list
- In the Output mapping: Double click the empty Original column and choose LAST_NAME from the drop down list
- In the Output mapping: Double click the empty Lookup column and choose LAST_NAME_PNEW from the drop down list
- Select the Scramble option for the If value not found then to scramble all last_names that cannot be found within the lookup table.
- Click OK to add the value lookup and close the function editor
If you're got stuck, you can also watch this short video to show you how to add value lookup functions. Make the video fullscreen to get a better view.