If you have specified a set of value or mask recodes during data profiling, and you want to use the same set of recodes in a data quality project, use attribute-based recode tables. This feature allows you to use a recode table from your profiling results.
Note: The maximum attribute length for table recodes is 2000 bytes (1000 characters in
UCS2).
During data profiling, you found that the data has a variety of formats for the
Product Type attribute. To standardize the format, you created the following mask
recodes in data profiling.
Attribute = Product Type
Value
|
Mask |
Recoding Mask |
Mask Recode |
MA8 4EE |
AAN NAA |
AA0N NAA |
MA08 4EE |
MA9 2DD |
AAN NAA |
AA0N NAA |
MA09 2DD |
MA4 2NN |
AAN NAA |
AA0N NAA |
MA04 2NN |
Now you want to use this recode to generate a recode table and ddx and apply them to
the Transformer.
To generate an attribute-based recode table
-
Provide recodes for specific values or masks for individual attributes in data profiling.
-
From the Navigation or Project View, right-click the Transformer process and select Edit Process. You can also double-click the process to open it for editing.
-
Select the Output Conditionals tab.
-
Right-click within the Transformation Rules pane (right-hand side) and select Add > New > Table Recode.
-
Enter a description for this table recode operation. The description will be inserted in the Statistics File.
-
Select Attribute as the source of recodes.
-
Select an entity and one of the entity's attribute which has recoded values. After the selection, a preview button (Open values table or Open mask table) will become enabled.
-
Click Open values table or Open mask table to display the attribute in a list view.
-
Review the recodes and the effect they have on the attribute's values. When you are satisfied with the recode, click Next.
-
Select the match attribute. This is the Transformer output attribute which will be matched against the original value in the attribute specified in Step 7.
-
Select the destination attribute. This is the Transformer output attribute which will store the recoded value.
-
Select the recode type (literal or mask).
-
(Optional) Select Case sensitive matching to use case-sensitive matching. By default, matching is case-insensitive. The case sensitive option does not apply to masks, only to values.
-
Click Finish. The scheduler notification bar opens.
-
(Optional) Change the job name.
-
Do one of the following:
- To schedule the job to run immediately, click Now. The message closes
and the job begins running in the background.
- Click Later. The Set Date & Time scheduler window opens. To
immediately run the job in the background, click Run Now. To schedule a
time, select a starting date on the calendar and select a time you want the job to
run on the selected date. Click Submit to save the scheduled time.
- Click Cancel to cancel the task.
Note: All scheduled jobs are run in the background. Note the progress in the
Background Tasks List View.
The recode table and its ddx file will be created in path\
repository_name\projectN\tables
, where path is the path to the repository directory (specified during installation), repository_name is the name of your repository and N is the number of your project. For example, C:/ProgramData/Trillium Software/.../repository1/project1/tables
.
When the Transformer is run, it will apply the recodes to the output attributes.