This topic describes (or links to) all functions available in the Expression Builder window that you use when working with the following:
- Data filters
- Business rules
- Conditional statements
- Attribute transformations
Function Categories
Functions are available for the following data categories:
- Aggregate. Available in the input row filters and conditionals for the Set Selection Utility, which operates on groups or sets of similar attributes. For information, see Expression Builder Functions for Sets of Records.
- Date/Time. Converts date and time periods into seconds and compares dates in different formats.
- Date/Time (Format). Expanded Date and Time functions. The available functions include adding or subtracting from a date/time, and calculating the difference between dates or times.
- Metadata. Functions that refer to attribute metadata values. Use expression qualifiers to verify the expected attribute value. If the attribute metadata has not been analyzed (for example, when the data is not loaded), the function verifies a default value based on the metadata type.
- Numeric. Performs calculations on numeric values. See also, About Precision and Accuracy of Numeric Data.
- Set. Determines if a value is equal to one in a list of values.
- Statistical. Performs statistic analysis on numeric data in an attribute.
- String. Performs calculations on string values.
- Transformation. Converts values into metaphones, patterns, soundexes, and masks, and standardizes values by datatype.
- Type. Verifies values for numeric, null (empty), unique, and Boolean expressions, and converts integers to decimals and decimals to integers.
- Quality. Includes MATCH , PROXIMITY and DEDUPE functions used in TS Quality processes. Quality functions also include those designed to work in Quality processes.
Guidelines
When using functions in the Expression Builder, note the following:
- All function names are case sensitive.
- All functions handling string values are case sensitive.
- Expressions that are true return one (1); expressions that are false return zero (0).
- Expressions used for profiling activities verify data only and must return a true or false result.
- Do not use attribute names that share a function keyword; for example, Metaphone, Type, and Average. The Expression Builder will recognize these words as function keywords only. When necessary, change an attribute's name before using it in a business rule, conditional, or other type of expression.
- When using literal values (for example, New York) enclose the values in double quotation marks ("New York"). Attribute names and numeric values do not require quotation marks; numeric values enclosed in quotation marks (for example, "123") are read as a literal value.
- Consider including the TRIM function when you need to ensure there are no leading or trailing spaces that may affect expression results, such as with numeric values; for example, ISNUMERIC(TRIM(LPACK(value))).
- To avoid introducing errors in expressions that include literal datatype values Decimal, Integer, and String, select them from the Literals > Type list in the Expression Builder. This allows you to add the properly formatted datatype value to your expression. See the Function Descriptions table for information about which functions require a datatype.
- For information about how to generate the SPACES literal value in the settings file, click here.
- For additional guidelines, see Expression Elements.