Simple expressions

Formulas can be as simple as this one, which derives a new field based on the values of the fields After and Before:

(After - Before) / Before * 100.0

Notice that field names are unquoted when referring to the values of the field. Similarly, the following expression simply returns the log of each value for the field salary.

log(salary)

Complex expressions

Expressions can also be lengthy and more complex. The following expression returns true if the value of two fields ($KX-Kohonen and $KY-Kohonen) fall within the specified ranges. Notice that here the field names are single-quoted because the field names contain special characters.

('$KX-Kohonen' >= -0.2635771036148072 and '$KX-Kohonen' <= 0.3146203637123107 
and '$KY-Kohonen' >= -0.18975617885589602 and 
'$KY-Kohonen' <= 0.17674794197082522) -> T

Several functions, such as string functions, require you to enter several parameters using correct syntax. In the following example, the function subscrs is used to return the first character of a produce_ID field, indicating whether an item is organic, genetically modified, or conventional. The results of an expression are described by -> `result`.

subscrs(1,produce_ID) -> `c`

Similarly, the following expression is:

stripchar(`3`,`123`) -> `12`

It's important to note that characters are always encapsulated within single backquotes.

Combining functions in an expression

Frequently, CLEM expressions consist of a combination of functions. The following function combines subscr and lowertoupper to return the first character of produce_ID and convert it to uppercase.

lowertoupper(subscr(1,produce_ID)) -> `C`

This same expression can be written in shorthand as:

lowertoupper(produce_ID(1)) -> `C`

Another commonly used combination of functions is:

locchar_back(`n`, (length(web_page)), web_page)

This expression locates the character `n` within the values of the field web_page, reading backward from the last character of the field value. By including the length function as well, the expression dynamically calculates the length of the current value rather than using a static number, such as 7, which will not be valid for values with less than seven characters.

Special functions

Numerous special functions (preceded with an @ symbol) are available. Commonly used functions include:

@BLANK('referrer ID') -> T

Frequently, special functions are used in combination, which is a commonly used method of flagging blanks in more than one field at a time.

@BLANK(@FIELD)-> T