Harmonizing fields

In order to successfully combine tables in Analytics, you may need to first harmonize one or more fields in the two tables being combined.

What is harmonizing?

Harmonizing is the process of making the data structure of corresponding fields in separate tables identical – for example, standardizing the data type of the fields.

Harmonizing can also mean making the format of the values in two corresponding fields identical – for example, standardizing the use of hyphenation in ID numbers.

If the structure of corresponding fields, or the format of values in the fields, is not identical, jumbled data can result, the combining operation may not execute, or joins or relations may not match values correctly.

Using functions and computed fields to harmonize fields

Using Analytics functions to create computed fields is the primary technique for harmonizing fields. 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 combine data using any of these methods:

  • Join or relate for the common key field, use a harmonized field and an original key field, or two harmonized fields.
  • Append, extract and append, or merge create one or more harmonized fields and then extract by fields to convert the harmonized computed fields to physical fields populated with the actual computed values. Use the extracted tables with the physical fields in the data combining operation.

    For more information, see Extracting data and Extracting and appending computed fields.

Analytics functions for harmonizing fields

Analytics functions that you can use for harmonizing fields are outlined below. For more information about using a specific function, see the ACL Scripting Guide.

Analytics 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.

[ Back to top ]

Analytics 14.1 Help