Using Table Range - trillium_discovery - trillium_quality - 17.1

The table range function in the Transformer lets you validate the data using a predefined range in a table, and optionally lets you perform table recoding.

To validate data using table range

1. 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.
2. Select the Output Conditionals tab.
3. Right-click within the Transformation Rules pane (right-hand side) and select Add > New > Table Range.
4. Enter a Description for this table range operation.
5. In the Select range table files section, do one of the following to select the lookup table:

   a. If your table and ddx file are on the server, select these files by clicking Import from server.

   b. If your table and ddx file are on the client, select these files by clicking Upload from client.

   c. If you want to edit the range 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.

6. Select the Delimiter used in the table file. If your 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.
7. (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 None which turns off the default comment character of '#'. If the character is not on the list, select Other and enter the character in the text box.
   Note: This option only applies to delimited tables.
8. (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.
9. (Optional) Click Preview to display the range table.
10. Click Next.
11. Select the attribute from the Match attribute list. This is the source output attribute which will be matched against the reference value in the table. The range check is only performed if the values in the match attribute match the values in the range attributes.
    Note: You can match up to five (5) attributes. Specifying multiple attributes creates an AND condition.
12. Select the attribute from the to range attribute list. This is the attribute in the table where the reference values are described.
13. Select the data type (literal or mask).
14. (Optional) Select the Case sensitive matching box to use case-sensitive matching. By default, matching is case-insensitive. The case sensitive option does not apply to masks, only to literals.
15. Click Next.
16. Select the attribute from the Attribute to check list. This is the output attribute to compare to the range attributes in the table.
17. Select the attribute from the Attribute for range check result list. This is the output attribute to indicate whether the value in the Attribute to check field is in the valid range. When the value is in the valid range, 'Y' will be stored in this attribute. If the value is not in the valid range, 'N' will be stored in this attribute.
18. Select the attribute from the Start of range attribute list. This is the attribute in the table with the low range attribute.
19. Select the attribute from the End of range attribute list. This is the attribute in the table with the high range attribute.

    See example

20. Click Next.
21. (Optional) Select the attribute from the Copy recode attribute list. This is the attribute in the table that contains the recode values.
    Note: Step 21 - 22 are optional and required only when you perform table recoding.

    See example

22. Select the destination attribute from the to attribute list. This is the output attribute which will store the recoded value.
23. Click Finish.

    When the Transformer is run, it will compare the value in the Attribute to check against the values in the Start of range attribute and End of range attribute. And then it returns 'Y' or 'N' in the Attribute for range check result to indicate whether the value was found in the range. The range check will only be performed if the initial look-up is successful. The optional recoding will only be performed if both the look-up and the range check are successful.