When you right-click on a column heading and select Add transformation, you will be taken to the transformation function editor.
The left-hand menu contains two tabs: one for transformation functions and the other for connections.
You can either scroll down to find the required function or use the search box to find it then drag and drop in the area on the right.
Example - remove special characters from phone numbers:
icon will appear next to the Telephone header indicating that values in this column have been transformed. When creating/editing a function, the expression preview in the bottom-left corner allows you to see how the data will be transformed if you save your changes. If you're working on a workflow that contains a step that hasn't been executed, you have to click Execute to see the preview of the data.
You can also see how a transformation will behave by using sample/dummy data: click Single at the bottom and enter the values you want to test.
To revert the transformation, right-click on the column header and select Remove transformation.
You can either continue building on this transformation using the Workflow Designer by clicking Save as workflow or save the data as a .csv file by clicking Download as.CSV.
| Function | Description |
|---|---|
| Add period | Add a period to a date/time expression |
| After | Extracts from an input value, everything that follows the search value |
| And | The logical operator and: if both inputs are true it returns true |
| Before | Extracts from an input value, everything that precedes thesearch value |
| Calculate | Evaluate a simple mathematical calculation using +, -, /, * and % |
| Add | Add |
| Divide | Divide |
| Multiply | Multiply |
| Remainder | Remainder |
| Subtract | Subtract |
| Check | Tests whether a value is valid with any of a wide range of validation tests |
| Alphabetic | Checks that the input value is alphabetic |
| Alphanumeric | Checks that the input value is alphanumeric |
| Date | Checks whether the value if of date data type |
| Decimal | Checks whether the value is a decimalNote: Integers don't pass this test |
| Empty | Checks whether the value is empty (spaces or Null) |
| Error | Checks whether the value is drilldown error |
| Even | Checks whether the value is an even number |
| False | Checks if the input is a False value |
| Integer | Checks whether the value is an Integer |
| Leap Year | Checks that the input value is a leap year |
| Negative | Checks that the input value is a negative number |
| Null | Checks that the input is a null value |
| Number | Checks that the input value is a number |
| Odd | Tests if the value is an odd number |
| Positive | Checks that the input value is a positive number |
| True | Checks that the input is a true value |
| Valid CUSIP | Checks if the input is a valid Id defined by the committee on uniform security identification procedures (CUSIP) |
| Valid International Securities Identification Number | Checks if the input is valid International Securities Identification Number (ISIN) |
| Valid Stock Exchange Daily Official List | Returns true if the input is valid stock exchange daily official list(SEDOL) number |
| Warning | Checks whether the input value is a drilldown warning |
| Chunk | Splits the input value into variable length chunks using thesupplied length, returning a list of split values |
| Combine lists | Joins two lists of values in different ways |
| Difference | The symmetric difference, leaving the values from the left andright that don't intersect |
| Intersection | The intersection of the two lists leaving only values that matchon both sides |
| Remove left | The values from the right list that don't intersect the left list |
| Remove right | The values from the left list that don't intersect the Right list |
| Union | The two lists combined |
| Common in list | Analyses a list and return two values. The first is most or least common in the list, and second is how often it occurred |
| Least common in list | Returns a list of two values by analyzing a list for the least common value. The first value on the returned list is the least common value and the second is number of times it occurs |
| Most common in list | Returns a list of two values by analyzing a list for the most common value. The first value on the returned list is the most common value and the second is number of times it occurs |
| Common prefix | Compares all supplied values and returns the prefix that is common to all of them |
| Compare | Collections of expressions to perform validations. These checks are particularly useful for the text field. |
| Contains | Checks whether the input value contains the check value, optionally ignoring case |
| Ends with | Checks whether the input value ends with a given suffix, optionally ignoring case |
| Equals | Checks the input value for equality with the comparison value, optionally ignoring case |
| Equals (standardized) | Checks the input value for equality with the comparison value,based solely on appearance i.e. ignoring any differences in datatype |
| Equals error text | Checks if the input values equal a specified error message |
| Equals warning text | Checks if the input values equal a specified warning message |
| Greater than | Checks if the input value is greater than the comparison value |
| Greater than or equal | Checks if the input value is greater than or equal to thecomparison value |
| Less than | Checks if the input value is less than the comparison value |
| Less than or equal | Checks if the input value is equal to or less than the comparison value |
| Matches expression | Checks if the input matches a regular expression |
| Matches format | Checks if the input matches a format pattern |
| Sounds like | Checks whether the input value phonetically matches the comparison value |
| Starts with | Checks whether the input values start with a given prefix, optionally ignoring case |
| Compare date/time | Compares two dates/times and returns the difference in unit |
| Concatenate | Concatenates two variables to form a new single variable |
| Constant value | Returns a specified value |
| Contains match | Returns true if any element of a list is contained in a lookup column. The following match types are available:
Three input types are available:
|
| Convert | Converts input into another datatype, as selected by the user |
| Auto convert | Auto converts |
| To alphanumeric | Converts any value to an alphanumeric value |
| To date | Converts any value to a date value. For example: 25.12.2009 to 25-Dec-2009 |
| To decimal | Converts any value to a decimal value |
| To integer | Converts any value to an integer valueFor example: Input Value-12.3 Return Value-12 |
| To null | Converts any blank spaces to null ones |
| To spaces | Converts any null values to blank ones |
| To time | Converts any value to a time periodFor example: Input Value:00-00-23 Return Value-00:00:23 |
| Convert to date | Converts any value to a date value. For example: 25.12.2009 to 25-Dec-2009 |
| Create date/time stamp | Creates a time stamp with supplied value year, month, day, hours, minutes, secondsFor example: Year-2009, Month-12, Day-25, Hours-12, Minutes40, Seconds-30to 25-Dec-2009 12:40:30 |
| Create list | Creates a value list from any number of values |
| Current row | Returns the row ID, starting at 1, i.e. the current row is displayed |
| Current timestamp (dynamic) | Returns the current timestamp |
| Datatype | Returns the datatype of the input value |
| Decode geohash coordinates | Decodes a geohash to its latitude or longitude component |
| Delimited substring | Returns the substring of a value using a start delimiter or an index position and either an end delimiter or return value length. For example:Input value: ABC,DEF, GHIDelimiter:,Length:5Returns value: DEF,G |
| Difference | Returns the portion that is a difference between two values |
| Edit distance | Calculates the Levenshtein edit distance between the input value and the compare value returning the number of edits required to transform the input value to the compare value and vice versa. For example, the edit distance between Frank and Plank is 2, because two characters must be changed to go from Frank to Plank and vice versa. Edits are not just transpositions but also removals and insertions. |
| Edit distance percentage | Calculates the Levenshtein edit distance between the input value and the compare value returning a percentage similarity between the two values |
| Jaro edit measure | Calculates the Jaro-Winkler distance between the input value and the compare value returning a percentage match. The higher the match the more similar the values are. |
| Jaro-Winkler edit measure | Calculates the Jaro-Winkler distance between the input value and the compare value returning a percentage match. The higher the match the more similar the values are. The Jaro-Winkler algorithm is the variant of Jaro algorithm and includes additional checks which test for a common prefix at the start of both the values. |
| Error message | Returns an error message, setting the expression into error and displaying the result in red. |
| Escape | Escapes or un-escapes a string based on a given escaping style. |
| Example phone number | Generates an example phone number for a specified region in various formats |
| Expand list | Extract all members of a list and separate them with a specified delimiter |
| Extract matches as list | Extracts elements from the input that are contained in a lookup column and outputs them as a list. The following match types are available:
Three input types are available:
|
| Extracts from list | Parses a value that is a list of comma separated values allowing any value to be extracted from the list by given index number |
| Extracts timestamp element | Extracts a defined element from a date/time value |
| Get age | Age |
| Get century | Century |
| Get century | Get a defined week from a date/day value |
| Get day of week name | Get a defined name from a date/day value |
| Get days | Days |
| Get hours | Hours |
| Get millis | Milliseconds |
| Get minutes | Minutes |
| Get month name | Get a defined name from a date/month value |
| Get months | Months |
| Get seconds | Months |
| Get weeks | Weeks |
| Get years | Years |
| First non-null | Given a variable number of input values, it returns the first valuethat is not null (empty) |
| Format phone number | Properly format a phone number or extract a specific attribute from it |
| Format date | Formats a date/time with custom date format |
| Geohash coordinates | Converts latitude and longitude to a geohash |
| Get cell | Returns a value from a cell at a given row/column position from the source of current drilldown |
| Hash code | Returns a generated MD5 hash code for the input value |
| If then else | Checks the condition field for the value of true. If its true returns the condition met or else. Can also have multiple condition fields to be evaluated if a precursory condition is not met |
| In length range | Checks whether the input is within the specified start and end length range |
| In range | Checks whether the input is within the specified start and end range, optionally ignoring case |
| Index of difference | Returns the position where two values begin to differ, starting from 1 or 0 if there is no difference |
| Initials | Returns the initials by splitting words from the input value with an optional list of delimiters (default is white space) |
| Insert | Insert a value into the input value at a given position |
| Length | Returns the number of characters in the given string |
| List | Transform a list of values using a variety of operations. Multiple operations can be used in a cumulative manner. |
| Common Prefix | Compares all supplied values and returns the prefix that is common to all of them |
| Deduplicate | Deduplicate all values in a list |
| Reverse | Reverse all values in a list |
| Reverse sort | Reverse sort all values in a list |
| Sort | Sort all the values in a list |
| List frequency | Returns unique values in the list interleaved with their count (frequency) |
| Lookup | Looks up values in a specified column in the lookup table and applies an operation based on a selected column for each of the matching rows. Note that this function replaces and deprecates Lookup List and Lookup Value functions. Where the value in the return column is numeric (integer or decimal), the applied operations include aggregation operations (e.g. maximum or average) on the specific return column for those values on the rows where the lookup value appears in the lookup column. For non-numeric values, operations include getting the first matched value or returning a list of all the values. The Aggregation Type option allows you to define what actions to perform on the selected Aggregate Columns. These are the supported types:
|
| Lookup check | Looks up values in a specified column in a lookup table and checks whether those values exist in the lookup column. Returns True or False only. |
| Lookup count | Looks up values in a specified column in a lookup table and returns a count for the number of times a given value occurs in the lookup column. Returns an integer value of 0 or greater. |
| Multi compare | Performs multiple compare operations in one transformation. Returns true if any of the individual arguments are true. |
| Contains | Checks whether the input value contains the check value |
| Ends with | Checks whether the input value ends with a given suffix |
| Equals | Checks the input value for equality with the comparison value |
| Equals (standardized) | Checks the input value for equality with the comparison value(s) based solely on appearance i.e. ignores differences in datatype. |
| Equals error text | Checks if the input value equals a specific error message |
| Equals warning text | Checks if the input value equals a specific warning message |
| Greater than | Checks if the input value is greater than the comparison value |
| Greater than or equal to | Checks if the input value is greater than or equal to the comparison value |
| Less than | Checks if the input value is less than the comparison value |
| Less than or equal to | Checks if the input value is less than or equal to the comparison value |
| Matches expression | Checks if the input value matches a regular expression |
| Matches format | Checks if the input value matches a format |
| Sounds like | Checks if the input value phonetically matches a comparison value |
| Starts with | Checks if the input value starts with any given prefix |
| Not | Turns the input value from true to false or vice-versa |
| Or | The logical operator or; if either input is true it returns true |
| Pad | Pads any value with a chosen character to achieve a given overall length, with the option of putting the padding before or after the given value. |
| Partition first | Returns the list of 3 values ['before', 'fragment', 'after'], where 'before' is the part of input value before the 1st occurrence of the fragment and 'after' is what's left. A fragment is a regularexpression. |
| Partition last | Returns the list of 3 values ['before', 'fragment', 'after'], where 'before' is the part of input value before the 1st occurrence of the fragment and 'after' is what's left. A fragment is a regularexpression. |
| Parse | Parses a value, extracting other values which match one of the supplied formats. This can be used to extract values that look like an expected type of value from a free-text value. For example, an embedded postal code in an address string. The result can be deduplicated, comma separated quoted list of values for each result found. Multiple patterns can be searched at the same time and they are searched in sequence. |
| Parse by format | Example: if the input value is 'abc1234def5678' and the format pattern to search for is '9999', then the result will be '1234,5678'. |
| Parse by regular expression | Example: if the input value is 'abc1234abc5678' and the expression to search for is 'a.c', then the result will be 'abc,abc'. |
| Power of n | Returns the nth power of the input value |
| Position | Returns the first index position, starting from 1, of a search value within the input value |
| Phone number match | Tests if two numbers represent exactly the same number |
| Phone number match code | Compares two numbers and returns the match code |
| Remove matches | (lookup function) Removes elements from the input that are contained in a lookup column.The following match types are available:
Three input types are available:
|
| Remove noise | Returns the input value after standardizing it by removing noise from the value. Initially, the value is transformed by translating all letter to uppercase and retaining only letters and digits. |
| Repeat | Returns an input value repeated a defined number of times.Example:Input Value: ABCRepeat: 3Return Value: ABCABCABC |
| Replace | Replaces all the instances of the search value with the replacement value. Example:Using the input value 'ABC:DEF:GHI', the search value ':' and the replacement value ';' will return 'ABC;DEF;GHI' |
| Regular expression replace | Replaces all the instances of the search value with the replacement value. Example:Using the input value 'ABC::DEF:::::::::GHI', the search value ':+' and the replacement value ';' will return 'ABC;DEF;GHI' This differs from the 'replace' function as the search value can be a regular function as opposed to a constant value. |
| Replace first | Replaces first the instances of the search value with the replacement value. Example:Using the input value 'ABC:DEF:GHI', the search value ':' and the replacement value ';' will yield the result 'ABC;DEF:GHI' |
| Replace matches | (lookup function) Replaces elements from a list with matching elements from a lookup table.The following match types are available:
Three input types are available:
|
| Row count | Returns the total number of rows in a view |
| Round number | Sets the number of decimal points after a decimal value |
| Split | Splits a value using another value in the field separator |
| Strings between | Searches a value for substrings delimited by a start and end tag, returning all matching substrings in a list |
| Strip substring | Strips the supplied substring value from the given input value if present. Example:If input value is 'abcdefghi' and search value is 'def', the result would be 'abcghi'. |
| Substring | Returns substring of a given value specified by giving start and end positions |
| Tag a value | Adds the start and end value to the input value if they are not present in their respective positions. Example: Input value: 12345> Start Value: < End Value: > Return value: <12345> |
| Transform number | Transforms a number using any of a wide selection of mathematical and scientific conversions |
| Absolute | Returns the absolute value of the integer |
| Arccosine | Returns the trigonometric arc cosine of the input as an angle in radians, in the range of 0 through to pi |
| Arcsine | Returns the trigonometric arc sine of the input as an angle in radians, in the range of -pi/2 through to +pi/2 |
| Arctangent | Returns the trigonometric arctangent of the input as an angle in radians, in the range of -pi/2 through to +pi/2 |
| Cosine | Returns the trigonometric cosine of an angle in radians |
| Cube | Returns the cube of a number |
| Cube root | Returns the cube root of a number |
| Euler raised | Returns Euler raised, e, raised to the power of input value |
| Log base 10 | Returns the logarithm of any number, to base 10 |
| Natural log | Returns the natural logarithm of any number, which is the log to base e |
| Negate | Converts a positive number to negative and vice-versa |
| Precision | Returns a numerical precision of input value - a number of significant digits |
| Random integer | Returns a random integer between 1 and the inputted value |
| Random number | Returns a random number between 0 and the supplied maximum value |
| Scale | Returns a numeric scale of a value - a number of significant digits after the decimal |
| Sine | Returns the trigonometric sine of an angle in radians |
| Square root | Returns the square root of a number |
| Sum digits | Returns a sum of numeric digit of any value. Example: Input value: 546.2 Sum digits: 17 |
| Tangents | Returns the trigonometric tangent of an angle in radians |
| Transform text | Transform a text value using a variety of operations. Multiple operations can be used in a cumulative manner. |
| Compact spaces | Reduces multiple spaces in input value to single space |
| Double Metaphone | Implement double Metaphone algorithm developed by Lawerence Phillips |
| Double Metaphone (alternate) | Implement alternate double Metaphone algorithm developed by Lawerence Phillips |
| Extract integer | Extract all values that are numbers out of embedded space or letters. Contiguous integers are extracted. |
| Fingerprint | Returns a fingerprint of an input value |
| Format pattern | Returns a format pattern of an input value |
| From hex to text | Converts a hexadecimal input into a string |
| From text to hex | Converts a string input into its hexadecimal value |
| From Unicode to text | Converts a Unicode input to a string |
| From text to Unicode | Converts an input string into its Unicode value |
| Improved Metaphone | Implements the improved double Metaphone algorithm |
| Improved Metaphone (alternate) | Implements the improved double Metaphone algorithm for alternate value |
| Lowercase | Converts to lowercase |
| Refined Soundex | Implements refined Soundex algorithm |
| Remove noise | Returns the input value after standardizing it by removing noise from the value. Initially, the value is transformed by translating all letter to uppercase and retaining only letters and digits. |
| Remove unprintable characters | Removes unprintable characters in the input value |
| Remove whitespace | Remove whitespaces in an input value |
| Remove simple format pattern | Simple format pattern is just like a format pattern, just that, it reduces multiple format characters to just one. Example: Format: AAAAA9999AA Simple format: A9A |
| Soundex | Soundex is an encoding used to relate similar names, but can also be used as a general-purpose scheme to find words with similar phonemes |
| Standardize | Standardizes a value by removing leading, trailing and compacting spaces. The Double Metaphone and alternate encoding is used for better phonetic representation. It also extracts integers and fingerprints, facilitates HEX, text and Unicode conversion as well as handling accents and simplifying data by applying simple format patterns. |
| Strip accents | Removes diacritics (~= accents) from a value |
| Title case | Converts to title case |
| To proper case | Converts to proper case where the first letter is in uppercase whereas all others are in lower case |
| Uppercase | Converts to uppercase |
| Trim | Removes all the instances of a single character from the start of the input value |
| Unquote value | Removes the quote value from the start and/or end of the input value, but leaves the respective part of the value alone if the quote is not present. Example: Input value: ABC12345ABC Value to remove (case sensitive): ABC Returns value: 12345 |
| Validate phone number | Validates a phone number according to a specific test |
| Warning message | Returns a warning message and displays the result in yellow |
