Transformation scripting

Transfornation scripting is an advanced features for processing data during import. Thanks to transformations cripts you can:

• cleanup values
• change values
• create new columns with values computed from other column(s)
• mark values with flags and notes
• build intelligence for handling values according to column names

Transformation scripts syntax resembles the Groovy and Java syntax. It is designed to be readable and effective but not strict. Users dfamiliar with scripting and programming should know, that some of the common features (like loops) are absent because of security and performance reasons. Script can be applied to and import column. The basic function is transforming the input value of a column row to another value.

Transformation scripts basics

• You can use variables in scripts for storing values and working with them
• You can use conditions and branching
• You can use wide pallete of functions

Our first script using variable and function:

val = value()
return trim(val)

This script trims whitespaces from the beginning and end of the column value of each data import cell. You can see, that the last line contains command return. The function of the script can be described as "pick a value from the current cell, apply function trim and return the result".

Variables

You can store values into variables and use them into expressions or as a script result. The typing of variables is dynamic thus in most of the cases there is no need to think of a variable type. 

Example of declaring universal variables which are dynamically typed. You can assign values to variables for later use:

a = value()
b = 12
c = 100.56
bool = false
e = "Hello"
x = b + c
return e + " world"

You can retype a variable or function return by adding as nameOfType:

double b = value() as double

boolean

String

int

double

How to access the column values?

Accessing the value we want to process is a key issue. Scripts provide a function value() which returns the current value. There are more advanced possibilities to access values:

• Function value(x) returns value of a x-th column of the import
• Function name() returns name of the current column
• Function name(x) returns name of the x-th column of the import
• Function format() returns format name of the current column

The values function returns a String value. String cannot be handled as a number unless it is properly converted. The conversion usually consists of normalization (ex.: round all numbers to 2 decimal places) or cleanup (ex.: replace all "," with ".").

More scripting language basics

• Math, formulas, expressions
• Conditions and branching
• Work with lists and maps

Transformation scripts topics

• String cleaning and normalization functions
• String manipulation

Advanced script tools

• Transformations over multiple columns
• External sources functions