Data structure and data format requirements

Data structure, or record structure, refers to the fields (data elements) contained in a record, the number and order of fields, and the data type and length of fields. Fields are the individual units of data in a record, such as first name, last name, address, vendor ID, and so on. Data format refers to certain characteristics of the values contained in fields, such as justification, case, and the format of dates.

Combining data in ACL requires that the following records or fields in the tables being combined have an identical data structure:

In addition, joining and relating require that values in common key fields be identically formatted in order for ACL to correctly match values.

If the data structure or the data format of fields differ, you may be able to use ACL functions to harmonize the fields.

For information about using the DISPLAY command to compare the data structures of two tables, see Comparing data structures.

Table 1 summarizes data structure requirements and Table 2 summarizes data format requirements when combining data.

Table 1. Data structure requirements

Requirement

Extract/Append

(entire record, fields in view, or selected fields)

Merge

(entire record)

Join

(common key field)

Relate

(common key field)

Fields (data elements

must be the same fields

must be the same fields

must be the same fields

must be the same fields

Number and order of fields

must be identical

must be identical

not applicable

not applicable

Data type of corresponding fields

must be identical

must be identical

must be identical

automatic harmonization of character-numeric joins

must be identical

Field length of corresponding fields

must be identical

must be identical

must be identical, or recommended to be identical, depending on data type

automatic harmonization of the length of character key fields

recommended to be identical

Table 2. Data format requirements

Requirement for corresponding fields

Extract/Append

(entire record, fields in view, or selected fields)

Merge

(entire record)

Join

(common key field)

Relate

(common key field)

Date/Datetime format

must be identical

must be identical

can differ

can differ

Justification

can differ

can differ

differences affect sort order of key field

must be identical

must be identical

Case

can differ

can differ

differences affect sort order of key field

must be identical

must be identical

Format of field value

(Standardized hyphenation, street type abbreviation, etc.)

can differ

can differ

differences affect sort order of key field

must be identical

must be identical

Related concepts
Harmonizing fields
Appending results to ACL tables and text files
About extracting and appending data
About merging tables
About joining tables
About relating tables
Related tasks
Comparing data structures


(C) 2013 ACL Services Ltd. All Rights Reserved. | Send feedback