Substring

Description

Select a specific portion of an input by choosing the beginning and end points for all characters in that column.

Calculation Editor

Options

Input Parameter Values	Input Values
Start Index	Column or Literal (Required Integer)
Length	Column or Literal (Required Integer)
Handle Errors	Auto-correct, Blank resulting value, Report error
Only Rollup If	Generic Criteria

Input

Input	Input Values	Required
Input 1	Column or Literal	✔

Remarks

Handle Errors Options: View examples below.

• Auto-correct: If your start index or length do not match the input value(s) that are being used, the auto-correct option will identify the needed fix and evaluate the substring calculation accordingly.
  • If the start index is greater than the length of the string, return a null string.
  • If the start index is less than one, the start index will be set to one automatically.
  • If the length is greater than the remaining characters in the string, just the remaining characters will be returned.
• Blank Resulting Value: If your start index or length do not match the input value(s) that are being used, the blank resulting value option will return blank values in those records.
• Report Error: If your start index or length do not match the input value(s) that are being used, the report error option will return a grey box in all cells that had errors. This grey box will explain the error if selected so the calculation can be edited.

Examples

Example 1

• Input: CAL87105SUB
• Design > Calculations > Substring
  • Start Index: 4
  • Length: 5
  • Handle Errors: Auto-correct
• Output: 87105

Example 2

• Input:
  • Row 1: 8502Vendors Incorporated
  • Row 2: 9133ABC Vending
• Design > Calculations > Substring
  • Start Index: 5
  • Length: 25
  • Handle Errors: Auto-correct
• Output:
  • Row 1: Vendors Incorporated
  • Row 2: ABC Vending

The auto-correct handled the length being 25 even though the values weren't 25 characters long by using the following logic: If the length is greater than the remaining characters in the string, just the remaining characters will be returned.

Example 3 (Utilizing alternative Handle Error methods)

• Input: 4423USX14K
• Design > Calculations > Substring
  • Start Index: 5
  • Length: 8
  • Handle Errors: Blank Resulting Value
• Output: Shows as blank
  • Reasoning: The number of total characters in the input string is shorter than 13 which is how much the start index and length requested.

Next, let's just change the "Handle Errors" setting to "Report Error".

• Output: See below.