Data Transformations
Create Lookup Map
This creates a lookup map using the supplied column of a data stream passed to it. It returns a map. If you assign the map to a variable using the “Assign to” property you can use the map in subsequent steps such as Lookup Transform.
Input parameters:
Key Columns - Names of the columns on which the map will be keyed. For now we only support a single key column.
Lookup Transform
Operation which will map certain columns of the stream passed to it with a map provided.
Input parameters:
Key Columns - Names of the key columns on which the map will be queried. For now we only support a single key column.
Value Columns - Names of the columns that will be extracted from the map row and appended to the output row. You can use the “SourceName AS DestName” syntax to output the extracted column with a new name.
Lookup Map - Map of Key/Row pairs supplied by Create Lookup Map operation. Could also be a simple Key/Value map, in which case the mapped value will be appended to the output row as a single new column with the name supplied by the Value Columns parameter.
Substring Transform
Applies a substring operation on a specified data column.
Input parameters:
Columns - Name of the column(s) to be affected by the operation.
Start - Position of the starting letter of the substring operation. The first letter has the index of 1.
Length - Relative length from the start position of the substring operation.
Round Transform
Rounds a decimal number to a fixed number of decimal places.
Input parameters:
Columns - Name of the column(s) to be affected by the operation.
Decimal Places - The maximum number of decimal places that will remain after the decimal point. The table below gives the result of rounding 265.245 using the HALF_UP mode to different numbers of decimal places.
Rounding Mode -
DOWN - Round towards zero.
FLOOR - Round towards negative infinity.
HALF_DOWN - Round towards “nearest neighbor” unless both neighbors are equidistant, in which case round down.
HALF_EVEN - Round towards the “nearest neighbor” unless both neighbors are equidistant, in which case, round towards the even neighbor.
HALF_UP - Round towards “nearest neighbor” unless both neighbors are equidistant, in which case round up.
UP - Round away from zero.
UNNECESSARY - Does not perform any rounding. Instead generates an error if the value to round has more decimal places than the Decimal Places parameter.
An example of each rounding mode is shown below. 
Trim Transform
Trims any leading and trailing spaces in the specified column.
Input parameters:
Columns - Name of the column(s) to be affected by the operation.
Type Cast Transform
Converts the values in the specified column into the given Java type.
Input parameters:
Columns - Name of the column(s) to be affected by the operation.
Data Type - Select from dropdown the Java type to which the values will be converted.
Pattern - The format that your data will be transformed into. For example when transforming the string “23/01/1999” to a date you would use the Java SimpleDateFormat “dd/mm/yyyy”. When transforming numbers into Integer, Long, Float Double or Decimal use the Java decimal format. For more information see the links on date formating or number formating in the “See also” section below.
Blank Transform
Replaces any null or blank values in the specified columns.
Input parameters:
Columns - Name of the column(s) to be affected by the operation.
Replacement Text - The text that will replace any blank values in those columns.
Expression Transform
Using Expression Transform you can do any type of operation that is supported by Groovy.
Input parameters:
Expression - Any java or Groovy expression. E.g. ColumnName.toUpperCase() or ColumnName = “ExampleName”
Column Order
Rearranges the columns in the order specified.
Input parameters:
Columns - Comma separated list of column names in the desired order - e.g. Name, DOB, City, Address. You can also use to rename the column at the same time. E.g. Name, DOB as DateOfBirth, City, Address as StreetAddress
Column Rename
Renames the column headers with the list of values provided.
Input parameters:
New Names - Comma separated list of column names that will replace the existing header. A “blank” value will pass through the original column name unchanged. E.g. Name,,DOB,Address will not rename the second column and any subsequent columns after Address.
Column Remove
Removes the specified columns from the recordset.
Input parameters:
Columns - Comma separated list of column names that will be removed.
Sort Table Rows
Sorts the records supplied to it by previous operations.
Input parameters:
Key Columns - List of columns to be used as keys for sorting, It’s a comma-separated list e.g. “Name, DOB”. The sort applies an ascending order by default. If you want to sort in descending order, add DESC after the column name e.g. “Name, DOB DESC”
Example:
Let’s assume you have the below data and you want to sort it by Name and DOB.
You would just specify Name, DOB in the Key Columns field in the order of importance. If you first wanted to sort by DOB and then by Name, you would specify “DOB, Name”.
Learn More
Learn about other ORQA operations
Control Flow
Data Aggregation
Data Filters
Data Input/Output
Data Reconciliation
Data Transformations
File System
Web
XML
Groovy