A file-based recode table allows the user to use an external user-defined recode table. This method can be used if you created the recode table by exporting the recodes in data profiling, or if you have access to previously-created recode tables in the server or client. You can also create your tables manually.
- You can use a fixed length file or a delimited file with a schema (ddx) for your recode table.
- If the first character is a comment character, then the line is ignored.
- The maximum attribute length for table recodes is 2000 bytes (1000 characters in UCS2).
To use a file-based recode table
- 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 File as the source of recodes.
-
Do one of the following:
a. If you have the recode table and ddx file on the server, or if you created the recode table and ddx file by doing a recodes export from Baseline Analysis, select these files by clicking Import from server.
Note: If you exported your recodes, you must select from\export
.In
\tables\general_resources
there are five special recode tables that you can select:-
rcaugeo.tbl
– casing table for Australian geography -
rccageo.tbl
– casing table for Canadian geography -
rcusgeo.tbl
– casing table for US geography -
rcusnam.tbl
– casing table for US names -
rcusstr.tbl
– casing table for US streets
If you select one of these tables, the corresponding ddx file (
rc.ddx
) will automatically be selected.Note: These five casing tables replace the previously used STRSUB table. The STRSUB table is still supported for legacy projects.b. If your recode table and ddx file are on the client, select these files by clicking Upload from client.
c. If you want to edit the recode table or ddx file on the server, select the file by clicking Import from server, and then click Copy to Client. After editing the file on the client, click Upload from client and select the file you edited.
-
- Select the Delimiter used in the recode table file. If your recode table is a fixed length file, select None. If the delimiter is not on the list, select Other and enter the character in the text box.
-
(Optional) Select the Character to use as comment indicator. The Transformer will ignore any lines in the recode table that start with the character specified in this option. If you do not specify this option, the comment character is '#' by default. If your table has no comments, select Nonewhich turns off the default comment character of '#'.If the character is not on the list, select Otherand enter the character in the text box.
Note: This option only applies to delimited tables.
-
(Optional) Select Quotes if you want to use values enclosed with quotes in the table lookup. If you do not select this option, values enclosed with quotes are ignored as lookup values.
- See example
-
Data: "ABC"
Recode Table: "ABC",X
In this example, if you select Quotes the result will be X. If you do not select Quotes, then there is no recoding and the result will be "ABC".
- (Optional) Click Preview to display the recode table.
- (Optional) If you want to lookup only part of the attribute, select Use phrase separator. See Using a Phrase Separator for File-based Recode Tables for details. If you do not use this option, click Next.
-
Select the attribute from the Match attribute list. This is the Transformer output attribute which will be matched against the original value in the recode file specified in Step 6.
Note: You can match up to five attributes. Specifying multiple attributes creates an AND condition for the table recode operation. See Recoding Data Using Multiple Attributes.
- Select the attribute from the to recode attribute list. This is the attribute in the recode file where the original values are described.
- Select the recode type (literal or mask) and click Next.
- Select the attribute from the Copy recode attribute list. This is the attribute in the recode file that contains the new values.
- Select the destination attribute from the to attribute list. 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 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.