String modifications

If you are familiar with the functions in Excel, it will not take you long to learn the more than 100 available functions in TARGIT Data Discovery, as the two sets of functions are similar or almost identical.

As an example, let us assume that we would like to manipulate the data in the DSDemoSheet data source. Specifically, we would like to cleanse data in the very first column, containing transaction IDs – the No. column. Most of the IDs have been prefixed with “SPI”, and now we would like to remove this prefix.

To do this, we will need a number of conditional and text manipulating functions:

• if(statement_to_be_tested, value_if_true, value_if_false). Conditional function that can return different results.
• left(string, number_of_characters). Returns a substring with number of characters counted from the left.
• right(string, number_of_characters). Returns a substring with number of characters counted from the right.
• len(string). Returns number of characters of a string.

Tip: All functions have been documented on http://doc.targit.com in the Data Service / Formats / Formulas section.