Harmonizing fields

In order to successfully combine tables in ACL, you may need to first harmonize one or more fields in the two tables being combined. Harmonizing is the process of making the data structure of two fields identical – for example, standardizing the data type of the fields. Harmonizing can also mean making the format of values in two fields identical – for example, standardizing the use of hyphenation in ID numbers. If the structure of fields or the format of values is not identical, jumbled data can result, the combining operation may not execute, or joins or relations may not match values correctly.

Using ACL functions and computed fields is the primary technique for harmonizing data. For example, conversion functions allow you to convert fields from one data type to another. Other functions allow you to alter field length, justification, and case, and standardize the format of values in fields. Depending on the degree of discrepancy between two fields, you may have to use a series of functions to successfully harmonize the fields.

Once you have harmonized fields, you can join or relate tables using a computed field and an original key field, or two computed fields. For combining data using extracting and appending, or merging, you may first have to create one or two new tables using extract by fields to convert extracted computed fields to physical fields populated with the actual computed values. You can then extract and append, or merge, using one or both new tables.

A number of the functions you can use for harmonizing fields are outlined below. For more information about using a specific function, see the ACL Language Reference.

Table 1. Functions for harmonizing fields

Function

Category

Purpose

STRING( )

Data type conversion

(N to C)

Converts numeric data to character data.

ZONED( )

Converts numeric data to character data (ASCII zoned data format) and adds leading zeros to the data.

VALUE( )

Data type conversion

(C to N)

Converts character data to numeric data.

CTOD( )

Data type conversion

(C or N to D)

Converts character or numeric dates to date data.

CTODT( )

Converts character or numeric datetimes to datetime data.

CTOT( )

Converts character or numeric times to time data.

DATE( )

Data type conversion

(D to C)

Converts date data to character data.

DATETIME( )

Converts datetime data to character data.

TIME( )

Converts time data to character data.

STOD( )

Data type conversion

(serial N to D)

Converts serial dates to date data.

STODT( )

Converts serial datetimes to datetime data.

STOT( )

Converts serial times to time data.

SUBSTRING( )

Length adjustment

Extracts the specified portion of a string (which can be equivalent to the entire existing string). Can be used for shortening or lengthening field length. If the specified length is longer than the existing string, trailing spaces are added.

BLANKS( )

Creates a blank character string of the specified length. Can be used to add leading or trailing spaces to character data.

LTRIM( )

Length adjustment/ Justification

Removes leading spaces from character data.

TRIM( )

Removes trailing spaces from character data.

ALLTRIM( )

Removes leading and trailing spaces from character data.

RJUSTIFY( )

Right justifies character data, with any trailing spaces converted to leading spaces.

UPPER( )

Case conversion

Converts alphabetic characters to uppercase.

LOWER( )

Converts alphabetic characters to lowercase.

PROPER( )

Converts the first character of each word to uppercase, and the rest of the word to lowercase.

INCLUDE( )

Format modification

Extracts the specified characters from a string.

For example, you could extract just numbers from alphanumeric data.

REMOVE( )

Extracts the specified characters from a string, and retains the original string length by adding trailing spaces.

EXCLUDE( )

Removes the specified characters from a string.

For example, you could remove numbers from alphanumeric data, or remove hyphens from “123-45-4536” and output the string “123454536”.

OMIT( )

Removes the specified characters or substrings from a string.

For example, you could remove “Corporation”, “Inc.”, or “Ltd.” from vendor names.

INSERT( )

Inserts the specified characters into a string.

For example, you could insert hyphens into “123454536” and output the string “123-45-4536”.

SPLIT( )

Breaks character data into segments based on separators such as spaces or commas and extracts a specified segment.

CLEAN( )

Removes invalid characters such as tabs and carriage returns, and any specified characters, from a string, and all subsequent characters, and replaces removed characters with spaces.

REPLACE( )

Replaces every instance of an existing string with a new string. For example, you could replace “Rd.” with “Road”.

DEC( )

Specifies the number of decimal places for a numeric field.

Related concepts
Data structure and data format requirements
Appending results to ACL tables and text files
About extracting and appending data
About merging tables
About joining tables
About relating tables
Extracting and appending computed fields
Related tasks
Comparing data structures
Related reference
ACLScript function reference


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