How to assign specific masked values to original values

If you have data that you wish to replace specific occurences of a value with a specific masked value then this can be easily achieved using the DataVeil User SQL Value mask.

Let’s illustrate with a simple example.

In the table ‘customer’, shown below, we have a column called ‘Location’ that we need to mask each specific value with a specific replacement value.

lookup1_Proj_view

To achieve our objective we will need to:

  1. Create a lookup table
  2. Create a primary key on the lookup column in the lookup table
  3. Create a User SQL Value mask

Step 1) Create a lookup table

 

Create a table with two columns: an original value column and a column with the specific masked values to be used.

This has been done below in the table called ‘lookup_table’ with the two columns ‘original_value’ and ‘masked_value’. Of course, you can name these whatever you like.

lookup2_ref_table

Step 2) Create a primary key on the lookup column in the lookup table

 

This is really part of creating the table in the previous step but this step is crucial for performance (except for the smallest trivial tables) that I wanted to make sure it’s not overlooked.

Therefore, please make sure that you create a primary key (or unique index) on the ‘original_value’ column in the lookup table.

Step 3) Create a User SQL Value mask

 

Create a User SQL Value mask for the column to be masked, i.e. the ‘Location’ column in this example.

lookup3_add_UserSQLMask

When the mask form opens, you will first need to create a JOIN to the lookup table. This is done under the mask’s ‘Join’ tab.

lookup4_create_join

After selecting the required configuration from the combo boxes, please make sure to click on the ‘Add’ button so that the condition appears in the text box. It’s easy to forget to ‘Add’ the condition and later wonder why the join isn’t working. You can also add multiple conditions with each ‘Add’. This effectively AND’s these conditions together.

 

Finally, under the mask’s ‘User SQL Value’ tab, define the SQL code to perform the lookup.

Note that the column names are qualified with the ‘Table Alias’ values that are defined in the ‘Join’ tab. If you forget to use these aliases to qualify the column names then this will result in ‘ambiguous or unresolved column reference’ type error messages at run time.

lookup5_SQL

Click ‘OK’ to close the mask form and the configuration is complete.

 

After running the project you can see below that the original values have been masked with the exact replacement values that were defined in the lookup table.

lookup6_result