Function Reference > Functions > Function Overview
  

Function Overview

You can use aggregate functions in Mapping Configuration tasks. You can use all other functions in Mapping Configuration tasks and Data Synchronization tasks.
The transformation language provides the following function categories:

Aggregate Functions

Aggregate functions return summary values for non-null values in selected fields. With aggregate functions you can:
Use aggregate functions in Mapping Configuration tasks only.
The transformation language includes the following aggregate functions:
Use aggregate functions in Aggregator objects only. You can nest only one aggregate function within another aggregate function. Informatica Cloud evaluates the innermost aggregate function expression and uses the result to evaluate the outer aggregate function expression. You can set up an Aggregator object that groups by ID and nests two aggregate functions as follows:
SUM( AVG( earnings ) )
where the dataset contains the following values:
ID
EARNINGS
1
32
1
45
1
100
2
65
2
75
2
76
3
21
3
45
3
99
The return value is 186. Informatica Cloud groups by ID, evaluates the AVG expression, and returns three values. Then it adds the values with the SUM function to get the result.

Filter Conditions

Use a filter condition to limit the rows returned in a search.
A filter limits the rows returned in a search. You can apply a filter condition to all aggregate functions and to CUME, MOVINGAVG, and MOVINGSUM. The filter condition must evaluate to TRUE, FALSE, or NULL. If the filter condition evaluates to NULL or FALSE, Informatica Cloud does not select the row.
You can enter any valid transformation expression. For example, the following expression calculates the median salary for all employees who make more than $50,000:
MEDIAN( SALARY, SALARY > 50000 )
You can also use other numeric values as the filter condition. For example, you can enter the following as the complete syntax for the MEDIAN function, including a numeric field:
MEDIAN( PRICE, QUANTITY > 0 )
In all cases, Informatica Cloud rounds a decimal value to an integer (for example, 1.5 to 2, 1.2 to 1, 0.35 to 0) for the filter condition. If the value rounds to 0, the filter condition returns FALSE. If you do not want to round up a value, use the TRUNC function to truncate the value to an integer:
MEDIAN( PRICE, TRUNC( QUANTITY ) > 0 )
If you omit the filter condition, the function selects all rows in the field.

Character Functions

The transformation language provides the following character functions:
To evaluate character data, the character functions LOWER, UPPER, and INITCAP use the code page of the Secure Agent that runs the task.

Conversion Functions

The transformation language provides the following conversion functions:

Data Cleansing Functions

The transformation language provides a group of functions to eliminate data errors. You can complete the following tasks with data cleansing functions:
The transformation language provides the following data cleansing functions:

Date Functions

The transformation language provides a group of date functions to round, truncate, or compare dates, extract one part of a date, or perform arithmetic on a date.
You can pass any value with a date datatype to any of the date functions. However, if you want to pass a string to a date function, you must first use the TO_DATE function to convert it to a transformation Date/Time datatype.
The transformation language provides the following date functions:
Several of the date functions include a format argument. You must specify one of the transformation language format strings for this argument. Date format strings are not internationalized.
The Date/Time transformation datatype does not support milliseconds. Therefore, if you pass a date with milliseconds, Informatica Cloud truncates the millisecond portion of the date.

Encoding Functions

The transformation language provides the following functions for data encryption, compression, encoding, and checksum:

Financial Functions

The transformation language provides the following financial functions:

Horizontal Expansion Functions

Use a horizontal expansion function to create a horizontal macro expression.
Horizontal expansion functions use the following naming convention: %OPR_<function_type>%.
Horizontal expansion functions use square brackets ( [ ] ) instead of parentheses.
The transformation language provides the following horizontal expansion functions:

Numeric Functions

The transformation language provides the following numeric functions:

Scientific Functions

The transformation language provides the following scientific functions:

Special Functions

The transformation language provides the following special functions:
You can nest other functions within special functions.

String Functions

The transformation language provides the following string functions:

Test Functions

The transformation language provides the following test functions: