Top 30 Analytics functions
The top 30 functions in ACLScript are useful across a number of different tasks. Use these functions regularly to help you prepare, parse, convert, and harmonize data in your scripts.
Removing leading and trailing space
Character fields in Analytics tables often contain leading or trailing spaces because the field widths are fixed length. When you need to perform an operation using the data from a character field, you can remove these spaces so that the string only contains the actual data.
ALLTRIM( )
Returns a string with leading and trailing spaces removed from the input string.
Note
It is good practice to use ALLTRIM() on any character field that you are using as input for another function so that no leading or trailing spaces affect the returned value.
Example
The Vendor_Number field contains the value " 1254". You need to remove this extra space from Vendor_Number so that you can harmonize the field with data in another table.
COMMENT returns "1254"
ALLTRIM(Vendor_Number)
Synchronizing alphabetic case
String comparison in Analytics is case-sensitive, therefore it is useful to synchronize the casing of all data in a field before you perform any comparisons, joins, or relations using the data.
UPPER( )
Returns a string with alphabetic characters converted to uppercase.
Example
The Last_Name field contains the value "Smith". You need to make this value uppercase to compare with an uppercase value from another table.
COMMENT returns "SMITH"
UPPER(Last_Name)
LOWER( )
Returns a string with alphabetic characters converted to lowercase.
Example
The Last_Name field contains the value "Smith". You need to make this value lowercase to compare with an lowercase value from another table.
COMMENT returns "smith"
LOWER(Last_Name)
PROPER( )
Returns a string with the first character of each word set to uppercase and the remaining characters set to lowercase.
Example
The Last_Name field contains the value "smith". You need to display it as a proper noun in your output.
COMMENT returns "Smith"
PROPER(Last_Name)
Calculating and separating strings
When you need to extract a segment of data from a longer string, or test some information about the string such as its length or contents, use these functions.
SUBSTR( )
Returns a specified substring from a string.
Example
The GL_Account_Code field contains the value "001-458-873-99". You need to extract the first three bytes, or characters, from the string.
COMMENT returns "001"
ASSIGN v_start_pos = 1
ASSIGN v_length = 3
SUBSTR(GL_Account_Code, v_start_pos, v_length)
LAST( )
Returns a specified number of characters from the end of a string.
Example
The GL_Account_Code field contains the value "001-458-873-99". You need to extract the last two bytes, or characters, from the string.
COMMENT returns "99"
ASSIGN v_num_chars = 2
LAST(GL_Account_Code, v_num_chars)
SPLIT( )
Returns a specified segment from a string.
Example
The GL_Account_Code field contains the value "001-458-873-99". You need to extract the second segment of the code from the string.
COMMENT returns "458"
ASSIGN v_delimiter = "-"
ASSIGN v_segment_num = 2
SPLIT(GL_Account_Code, v_delimiter, v_segment_num)
AT( )
Returns a number specifying the starting location of a particular occurrence of a substring within a character value.
Example
The GL_Account_Code field contains the value "001-458-873-99". You need to determine the starting byte position of the value "458" to test whether the GL code's second segment is "458" (start position "5").
COMMENT returns "5"
ASSIGN v_occurrence = 1
ASSIGN v_substring = "458"
AT(v_occurrence, v_substring, GL_Account_Code)
OCCURS( )
Returns a count of the number of times a substring occurs in a specified character value.
Example
The GL_Account_Code field contains the value "001-458-873-99". You need to determine that the GL code is correctly formatted by ensuring the data contains three hyphen characters.
COMMENT returns "3"
ASSIGN v_substring = "-"
OCCURS(GL_Account_Code, v_substring)
LENGTH( )
Returns the number of characters in a string.
Example
The GL_Account_Code field contains the value "001-458-873-99". You need to determine that the GL code is correctly formatted by ensuring the data contains 14 characters.
COMMENT returns "14"
LENGTH(GL_Account_Code)
Converting data types
Depending on the data source and import statements that produced the Analytics table, you may need to convert values in a field from one data type to another so that an operation is possible. For example, to perform arithmetic on data that was imported as character ("12345"), you must convert it to numeric.
STRING( )
Converts a numeric value to a character string.
Example
The Invoice_Amount field contains the value 12345.67. You need to convert this to character data.
COMMENT returns "12345.67"
ASSIGN v_str_length = 8
STRING(Invoice_Amount, v_str_length)
VALUE( )
Converts a character string to a numeric value.
Tip
VALUE( ) is often used with ZONED( ) to add leading zeros.
Example
The Invoice_Amount field contains the value "12345.67". You need to convert this to numeric data.
COMMENT returns 12345.67
VALUE(Invoice_Amount, 2)
CTOD( )
Converts a character or numeric date value to a date. Can also extract the date from a character or numeric datetime value and return it as a date. Abbreviation for "Character to Date".
Example
The Submission_Date field contains the value "April 25, 2016". You need to convert this to datetime data.
COMMENT returns `20160425`
ASSIGN v_date_format = "mmm dd, yyyy"
CTOD(Submission_Date, v_date_format)
DATE( )
Extracts the date from a specified date or datetime and returns it as a character string. Can also return the current operating system date.
Example
The Submission_Date field contains the value `20160425`. You need to convert this to character data.
COMMENT returns "04/25/2016"
ASSIGN v_date_format = "MM/DD/YYYY"
DATE(Submission_Date, v_date_format)
Adding leading zeros
Convert numeric data to character data and adds leading zeros to the output when you need to harmonize fields that require leading zeros.
ZONED( )
Converts numeric data to character data and adds leading zeros to the output.
Example
The Employee_Number field contains the value "254879". You need to convert the value to a 10-digit string with leading zeros.
Tip
You must use the VALUE() function to convert the character to numeric data before using the numeric as input for ZONED().
COMMENT returns "0000254879"
ASSIGN v_str_length = 10
ASSIGN v_num_decimals = 0
ZONED(VALUE(Employee_Number, v_num_decimals), v_str_length)
BINTOSTR( )
Returns Unicode character data converted from ZONED or EBCDIC character data. Abbreviation for "Binary to String".
Note
Unicode edition only. For non-Unicode editions, see ZONED() above.
Example
The Employee_Number field contains the value "254879". You need to convert the value to a 10-digit string with leading zeros.
Tip
You must use the VALUE() function to convert the character to numeric data before using the numeric as input for ZONED(). You then use BINTOSTR() to convert the ASCII data returned from ZONED() to Unicode.
COMMENT returns "0000254879"
ASSIGN v_str_length = 10
ASSIGN v_num_decimals = 0
ASSIGN v_str_type = "A"
BINTOSTR(ZONED(VALUE(Employee_Number, v_num_decimals), v_str_length), v_str_type)
Extracting datetime parts
Use these functions to isolate and extract specific components of a datetime value.
MONTH( )
Extracts the month from a specified date or datetime and returns it as a numeric value (1 to 12).
Example
The Transaction_Date field contains the value `20160815 100252`. You need to extract the month as character data with a leading zero.
COMMENT returns "08"
ASSIGN v_str_length = 2
ZONED(MONTH(Transaction_Date), v_str_length)
DAY( )
Extracts the day of the month from a specified date or datetime and returns it as a numeric value (1 to 31).
Example
The Transaction_Date field contains the value `20160815 100252`. You need to extract the day as character data.
COMMENT returns "15"
ASSIGN v_str_length = 2
STRING(DAY(Transaction_Date), v_str_length)
YEAR( )
Extracts the year from a specified date or datetime and returns it as a numeric value using the YYYY format.
Example
The Transaction_Date field contains the value `20160815 100252`. You need to extract the year as a numeric value.
COMMENT returns 2016
YEAR(Transaction_Date)
HOUR( )
Extracts the hour from a specified time or datetime and returns it as a numeric value using the 24-hour clock.
Example
The Transaction_Date field contains the value `20160815 100252`. You need to extract the hours as a numeric value.
COMMENT returns 10
HOUR(Transaction_Date)
MINUTE( )
Extracts the minutes from a specified time or datetime and returns it as a numeric value.
Example
The Transaction_Date field contains the value `20160815 100252`. You need to extract the minutes as a numeric value.
COMMENT returns 2
MINUTE(Transaction_Date)
SECOND( )
Extracts the seconds from a specified time or datetime and returns it as a numeric value.
Example
The Transaction_Date field contains the value `20160815 100252`. You need to extract the seconds as a numeric value.
COMMENT returns 52
SECOND(Transaction_Date)
CDOW( )
Returns the name of the day of the week for a specified date or datetime. Abbreviation for "Character Day of Week".
Example
The Transaction_Date field contains the value `20160815 100252`. You need to extract the name of the day as character data.
COMMENT returns "Mon"
CDOW(Transaction_Date, 3)
CMOY( )
Returns the name of the month of the year for a specified date or datetime. Abbreviation for "Character Month of Year".
Example
The Transaction_Date field contains the value `20160815 100252`. You need to extract the name of the month as character data.
COMMENT returns "Aug"
CMOY(Transaction_Date, 3)
Manipulating strings
Remove or replace segments of character fields using these functions.
INCLUDE( )
Returns a string that includes only the specified characters.
Example
The Address field contains the value "12345 ABC Corporation". You need to extract the address number and exclude the name of the company.
COMMENT returns "12345"
ASSIGN v_chars_to_return = "0123456789"
INCLUDE(Address, v_chars_to_return)
EXCLUDE( )
Returns a string that excludes the specified characters.
Example
The Address field contains the value "12345 ABC Corporation". You need to extract the name of the company and exclude the address number.
COMMENT returns "ABC Corporation"
ASSIGN v_chars_to_exclude = "0123456789"
EXCLUDE(Address, v_chars_to_exclude)
REPLACE( )
Replaces all instances of a specified character string with a new character string.
Example
The Address field contains the value "12345 Acme&Sons". You need to replace the "&" character with the word " and ".
COMMENT returns "12345 Acme and Sons"
ASSIGN v_target_char = "&"
ASSIGN v_replacement_char = " and "
REPLACE(Address, v_target_char, v_replacement_char)
OMIT( )
Returns a string with one or more specified substrings removed.
Example
The Address field contains the value "12345 Fake St". You need to extract the address without the street suffix.
COMMENT returns "12345 Fake"
ASSIGN v_chars_to_omit = "St"
OMIT(Address, v_chars_to_omit)
REVERSE( )
Returns a string with the characters in reverse order.
Example
The Report_Line field contains the value "001 Correction 5874.39 CR ". You need to reverse the value and omit any leading or trailing spaces.
COMMENT returns "RC 93.4785 noitcerroC 100"
REVERSE(ALLTRIM(Report_Line))
BLANKS( )
Returns a string containing a specified number of blank spaces.
Example
You need to create a computed field for a region name based on a value in the region_code field. You must ensure that the default value you specify at the end of the command is at least as long as the longest input value.
COMMENT BLANKS returns a string of 8 " " chars
ASSIGN v_length = 8
DEFINE FIELD region COMPUTED
"Southern" IF region_code = 1
"Northern" IF region_code = 2
"Eastern" IF region_code = 3
"Western" IF region_code = 4
BLANKS(v_length)
