Create and manage standard codes (lookup tables)

5-minute quick start

Permissions

• Super administrators and data standard administrators can create and manage all lookup tables, lookup table folders, and the template library.

• Data standard set maintainers can edit or delete lookup tables that they have access to.

Limits

• The folder hierarchy for lookup tables cannot exceed 10 levels.

• The total number of lookup table folders across all levels cannot exceed 1,000.

• Each lookup table can contain a maximum of 10,000 code values. Code values within a single lookup table must be unique.

Lookup table list

The lookup table list provides two display modes: Edit and View. On the Reference Tables page, click the icon in the upper-right corner to switch between modes.

In View mode, you can view lookup table folders and their information, but you cannot perform any operations. This mode is ideal for browsing.

In Edit mode, in addition to viewing lookup table folders and lookup tables, you can perform operations such as viewing references, editing, exporting, and deleting lookup tables. You can also edit and delete lookup table folders. Only users with permission to manage lookup tables can use edit mode.

1. On the Reference Tables page, you can view lookup table folders and information. The lookup table folders on the left are organized by business or organizational structure to classify and manage lookup tables hierarchically. When you select a folder, the list on the right displays the lookup tables in that folder. The information includes the name, parent folder, description, status, definition method, number of code values, owner, the user who last updated it, and the time of the update. You can also click All lookup tables to view information about all lookup tables.

2. (Optional) You can search for a specific lookup table by its name or code. You can also filter lookup tables by parent folder or owner.

3. You can perform the following operations on lookup table folders and lookup tables.

   Operation		Description
   Lookup Table Folder	Create lookup table	Click the Create lookup table icon to the right of the folder row to create a lookup table in the current folder. If you are in the All lookup tables folder, the new table is created directly under it.
   	Import lookup table from template library	Click the Import lookup table from template library icon to the right of the folder row, or hover over the Create lookup table button and click Import lookup table from template library from the drop-down list. This lets you import a lookup table template into the current folder.
   Lookup table list	View Details	In the Actions column, click View Details to view the details and reference information of the lookup table.
   	Edit	In the Actions column, click Edit to modify the configuration of the lookup table. If you change the definition method, source table, or fields, the existing configuration is overwritten only after the data is successfully retrieved, saved, and published. Otherwise, the existing configuration is retained.
   	Export	In the Actions column, click Export, or select lookup tables from the list and click Batch Export at the bottom. This exports the lookup table and its code values to an Excel file for easy maintenance and sharing with business personnel. For more information about export configurations, see Batch import and export lookup tables.
   	Update code values	This operation is available only when the definition method is Import from table. In the Actions column, click More and select Update code values. The system automatically retrieves the latest code value data based on the import configuration. After successful retrieval, you must save and publish the changes to update the existing code value configuration.
   	Edit code values	This operation is available only when the definition method is Import from table. In the Actions column, click More and select Edit code values. You can directly modify the code value definitions. This is useful for making simple modifications to the original code value information imported from a table.
   	View References	In the Actions column, click More and select View References to see the details of how the lookup table is referenced by standard templates. Click the name of a Standard Template to quickly view its information, including properties, reference information, and version history.
   	Delete	In the Actions column, click More and select Delete. Deleting a lookup table cannot be undone. Proceed with caution.

Manage lookup table folders

1. In the top menu bar of the Dataphin homepage, choose Administration > Standard.

2. In the navigation pane on the left, choose Reference Data > Standard Codes (Lookup Tables). On the Lookup Table Folder tab, click the Edit icon.

3. In the Lookup Table Folder Management panel, you can create, move, edit, and delete lookup table folders.

   Operation	Description
   Create	Enter a name for the folder. Sibling folders under the same parent folder cannot have the same name. The name cannot contain the forward slash (/) character and must be within 128 characters. You can add a folder as a subfolder to a specified level. If you do not select a parent folder, the new folder is placed in the root directory of lookup tables.
   Edit	You can modify the name of the folder.
   Move	You can move the lookup table to a different parent folder.
   Delete	You cannot delete a folder if it contains subfolders or lookup tables.

Create a lookup table

1. On the Reference Tables page, click the + Create lookup table button.

2. In the Create Custom Lookup Table dialog box, configure the parameters.

   Parameter	Description
   Lookup table name	The name of the lookup table. It must be unique within its parent folder and no more than 128 characters. For example: National_Region_Code_Table.
   Lookup table code	A unique identifier for the lookup table. Enter up to 128 characters. You can use English letters, numbers, and any half-width symbols. For example: DQ001.
   Parent folder	Select the folder for the lookup table. If you are in a specific lookup table folder when creating the table, that folder is selected by default. If you do not select a folder, the table is placed in the root directory. To create a new folder, see Manage lookup table folders.
   Owner	Select the owner of the lookup table.
   Description	Add a description for the lookup table. Enter up to 1,000 characters.
   Code information	Supports two definition methods: Custom and Import from table. Create a custom lookup table Enter the information for the lookup table. You can also edit and delete the code information. Click the Add code value button to add more code values. Click the Batch entry button to add code values in bulk. Click the Delete all button to clear all code values. Batch entry: In the Batch entry dialog box, you can enter or paste code value information. The system parses a maximum of the first 10,000 rows. Each row is parsed in the following order: code value, code name, English name, and code description. Use a tab character (\t) or a space (\s) to separate parameters (columns). Use a line feed character (\n) or press Shift+Enter to start a new row. For example: Code value: 2900, Code name: Shanghai City, English name: Shanghai City, Code description: 2900 identifies Shanghai City. Code value: Required. Must be unique within the lookup table. Up to 64 characters. Code name: Required. Up to 64 characters. English name: Optional. Up to 64 characters. Supports uppercase and lowercase English letters, numbers, and the following special characters: Parentheses (()), caret (^), tilde (~), backtick (`), exclamation point (!), at sign (@), pound sign (#), dollar sign ($), percent sign (%), ampersand (&), asterisk (*), underscore (_), plus sign (+), backslash (\), hyphen (-), equals sign (=), square brackets ([]), curly braces ({}), vertical bar (|), semicolon (;), single quotation mark (''), colon (:), double quotation mark (""), comma (,), period (.), forward slash (/), angle brackets (<>), middle dot (·), and question mark (?). Code description: Optional. Up to 256 characters. Click Save and Publish to create the lookup table. Import from table Note When retrieving code value data, row-level permissions are not applied, and data is not masked. Select a data source that can be made public. When the definition method for a lookup table is Import from table, do not use partitioned tables. If you must use a partitioned table, use one where each partition stores the full data. Because each lookup table can contain a maximum of 10,000 code values, only the first 10,000 rows of table data that meet the conditions are queried. The current user must have permissions for the SELECT statement on the selected source table fields. The system automatically converts the data read from the source table to the string type for storage. If the source fields for the code name and code value cannot be converted, the entire record fails to be added. If the source fields for the English name and description cannot be converted, the corresponding configuration items are left empty, which does not affect the addition of the entire record. You can reference different data tables to quickly create lookup tables and reduce configuration costs. In the Code information area, configure the following information. Source table (Required): You can reference Dataphin tables (excluding meta tables and image tables) and data source tables that support data preview. You can perform a fuzzy search by table name or description, or click the filter icon to quickly filter data tables by business segment, project, data source, or table type. Code Value (Required): Select a field from the source table. The code value must be unique within the lookup table. If the source field contains duplicate values, the system automatically retrieves the data corresponding to the first record. If the source data exceeds 64 characters, the entire record fails to be added. Code Name (Required): Select a field from the source table. If the source data exceeds 64 characters, the entire record fails to be added. English name (Optional): Select a field from the source table. Supports uppercase and lowercase English letters, numbers, and special characters. If the source data exceeds 64 characters, the system automatically truncates it or leaves it empty. The special characters are: Parentheses (()), caret (^), tilde (~), backtick (`), exclamation point (!), at sign (@), pound sign (#), dollar sign ($), percent sign (%), ampersand (&), asterisk (*), underscore (_), plus sign (+), backslash (\), hyphen (-), equals sign (=), square brackets ([]), curly braces ({}), vertical bar (|), semicolon (;), single quotation mark (''), colon (:), double quotation mark (""), comma (,), period (.), forward slash (/), angle brackets (<>), middle dot (·), and question mark (?). Code Description (Optional): Select a field from the source table. If the source data exceeds 256 characters, the system automatically truncates it or leaves it empty. Data filtering (Optional): You can add filter conditions based on the fields of the selected table, such as partitions, null values, or duplicate values, to filter the source data. If the table is a MaxCompute source table or a Dataphin logical table, the system automatically enables and fills in the partition filter condition. For other source types, you can enable it as needed. After configuration, click the Preview data button to validate the parameters, permissions, and syntax. Parameters: Checks whether all required parameters are filled in. Permissions: Checks whether the current user has permission to create a lookup table. Filter condition: Checks whether the filter condition statement is correct. After successful validation, the lookup table data is retrieved and added to Dataphin. While the lookup table data is being retrieved, you can click Cancel or close the dialog box to stop the query and exit. The configured data will not be saved. If you click Previous, you will return to the previous step and stop the query. You will need to retrieve the data again. If data retrieval fails, you can click Retry to retrieve the data again, or click View log to see the reason for the failure. If data retrieval is successful, you can preview the code value information. Click Save and Publish to add the lookup table to Dataphin. If some records failed to be retrieved, an exception list is displayed where you can view the specific reasons.

What to do next

After creating a lookup table, you can select it when configuring value constraints for standard template properties. For more information, see Create and manage standard templates.