Using Table Maths

Table Maths generates a new table of data from an input table by applying formulas over each row. The number of rows in the output table will exactly match the number of rows in the input table.

  1. Input table: Click and select the table to use.
    • If a value is missing in a row of the input table, then the value of the variable for that column will be NaN if it is a numeric or flag column, or the empty string if it is a string column.
  2. Click the blue  (+) button to add new columns to the output table.
  3. Column Name: holds the name of the new column to be generated in the output table.
    • If the name of the column is not a valid variable name within formulas, then it may be renamed for use within the formula. If a column is renamed for use in the formula, then a warning will be displayed below the input table selector with the variable names that you can use to reference the renamed columns.
  4. Output Type: allows you to set the type of data that the formula produces. The output type can be left as auto, and the process will try to automatically determine the type of values that the formula is producing. Otherwise, you can explicitly select an output type, and the values that the formula produces will be converted to that type.
  5. Formula: holds the formula to be applied to generate the values for the column. This formula is applied on every row of the input table. The columns of the input table can be referenced by name for use within the formula. The syntax and rules for writing math formulas are explained later in this document.
  6. Click the Open Table Viewer button to view the current maths table.

Defining your own variables

If you are re-using the same value multiple times, or if you wish to organise your equations, defining your own variables can be very useful (and sometimes even more efficient!).

Variable Syntax:

  • The variable name appears on the left, followed by an equals sign (=), your expression, and finally a semi-colon (;).
  • Example:
     a = v1 * 2;
     res = a + sin(v1);
Click to copy
  • The semi-colon is very important. It must be there to separate the declaration of each variable.

Operators

Operator     Description

    +              Addition

    -               Subtraction

    *               Multiplication

    /               Division

   %              Modulo: A%B yields the remainder of A divided by B

    ^              Power: A^B raises A to the power of B

In the absence of any operator, multiplication is assumed. Thus, 5 a will be interpreted as 5 * a. For other operators, see also the Conditions sections for boolean operations, and the Bitwise Operators section below.

Bitwise Operators

Maths operations support a number of bitwise operators. These can be useful for setting flags in headers, but these are not boolean operations. Some care must be taken:

  • Only the integer part of a value is considered
  • Operations consider which bits are set in that integer value

As a consequence, these differ from conditional operations (if, && etc) which treat any non-zero value as one (true)

a xor b    Exclusive OR: sets bits in the output only where they differ in the inputs, according to the following table.

a & b    Bitwise AND: performs a logical AND on each bit in the inputs, and sets the corresponding output bit to the result.

a | b    Bitwise OR: performs a logical OR on each bit in the inputs, and sets the corresponding output bit to the result.

~a    1's complement: performs a bitwise NOT, inverting all bits in the value. Note that the integer representation is signed, so this is equivalent to (-a - 1).

* Simplified to show an 8 bit representation

Conditions

You can build complex expressions that result in different behaviour in different situations. Conditional statements take the form:

if  ( condition ,  true statement ,  false statement )

where the true statement is used if the condition is true, or vice-versa. Note that those statements can themselves be arbitrary expressions, such as another conditional statement.

Standard comparison operators may be used, which may be combined with standard boolean operators. You can preface any boolean expression with a ! to negate it:

<, >, <=, >= Comparison: Less Than, Greater Than, Less Than or Equal, Greater Than or Equal
!=, == Equality: Not Equal, Equal
&&, ||, ! Boolean: AND, OR and NOT.
isnan(...), isnull(...), ismissing(...) These functions will evaluate to "true" if the expression is NaN (missing/undefined value), otherwise "false"
isdefined(...), isvalid(...) These functions are the opposite of isnan/isnull/ismissing -- they will evaluate to true if the value is defined at this location (not NaN)

Note: Do not use != nan or == nan to check for invalid values in conditional statements. It won't do what you think! Use one of the isnan(), isnull(), isvalid(), etc. functions instead.

Conditional Examples:

if (a < 0 && b < 0, c, a + b)
Click to copy

If both a and b are less than zero, use the value of c; otherwise use the sum of a and b


if (a < 0, if (b < 0, c, a + b), a + b)
Click to copy

A more-complicated way of writing the same thing, using one conditional inside another.


if (isnan(a) || isnan(b), 1500, a + b)
Click to copy

If either a or b are NaN (invalid), use 1500; otherwise use the sum of a and b

Combining / Merging

The most natural-seeming way to merge two things together would be to "add" them, as in a + b + c. But this doesn't work, for two reasons.

First, in places where they overlap, that would give you the arithmetic sum of the values at that location -- which is probably not what you intend.

Second, missing values are generally represented by the value NaN (not-a-number). NaN plus any other value remains NaN, so this would have the effect of producing output only at locations where all inputs are defined. This is also probably not what you intend.

To simplify this, Insight provides several functions to make combining easy:

  • merge(a, b, c, ...) - combines any number of items, in order of precedence. i.e. where they overlap, the first available value in the list will be used.
  • min(a, b, c, ...) - combines any number of items, using the smallest available value
  • max(a, b, c, ...) - combines any number of items, using the largest available value
  • sum(a, b, c, ...) - arithmetic sum of any number of items, ignoring those that aren't defined at a location
  • avg(a, b, c, ...) - arithmetic mean of any number of items, ignoring those that aren't defined at a location
  • random(max) - generate a random number between 0 and max (inclusive)
  • random(min, max) - generate a random number between min and max (inclusive)
  • lerp(v0, v1, t) - linearly interpolates between two inputs (v0, v1) for a parameter (t) in the closed unit interval [0, 1]. Parameter t is clamped between 0 and 1.

Math Functions

Trigonometric
sin(x), cos(x), tan(x) sine, cosine, tangent of a value in radians
asin(x), acos(x), atan(x) arc sine, arc cosine, and arc tangent
atan2(y,x) arc tangent - returns the angle whose tangent is y/x
sec(x), cosec(x), cot(x) secant, cosecant, co-tangent
sinh(x), cosh(x), tanh(x), asinh(x), acosh(x), atanh(x) hyperbolic sine, cosine & tangent; inverse hyperbolic sine cosine & tangent
Logarithmic
ln(x) Natural logarithm
log(x) Log base 10
lg(x) Log base 2
exp(x) Exponential (e^x)
pow(a, b) or a^b Raise a to the power of b
Rounding
rint(x) Statistically round to the nearest integer (0.5 will always round towards the nearest even integer)
round(x) Arithmetically round to the nearest integer (0.5 will always round towards positive infinity)
floor(x) Floor (round down to the next integer)
ceil(x) Ceiling (round up to the next integer)
Miscellaneous
abs(x) Absolute value
rand(), random() Random number between 0 and 1
rand(max), random(max) Random number between 0 and max
rand(min, max), random(min, max) Random number between min and max
mod(x,y) The remainder after x is divided by y; this can also be expressed as x % y
gcd(x,y) The greatest common divisor of x and y
lcm(x,y) The lowest common multiple of x and y
sqrt(x) Square root
signum(x) Sign of a number (-1, 0, or 1)
toDegrees(x) Convert an angle in radians to degrees
toRadians(x) Convert an angle in degrees to radians

String Functions

contains(text, term) Checks whether a contains b (case-sensitive)
isEmpty(text) Checks whether text contains no characters
isBlank(text) Checks whether text contains only whitespace
startsWith(text, term) Checks whether text begins with term
endsWith(text, term) Checks whether text ends with term
lower(text) Converts text to lower
caseupper(text) Converts text to upper case
reverse(text) Reverses the characters in text
replace(text, target, replacement) Replaces all occurrences of target with replacement in text (case-sensitive)
formatDecimal(number) Formats number to text with 2 decimal places, and commas separating every 3rd digit
formatInteger(number) Formats number to text with no decimal places, and commas separating every 3rd digit
len(text) Gets the length of text (i.e. the number of characters)
asNumber(text) Converts the text containing a number (e.g. "1e3") into a number (e.g. 1e3)
sub(text, start) Gets a subsection of text from the start'th character (inclusive)
sub(text, start, end) Gets a subsection of text from the start'th character (inclusive) to the end'th character (exclusive)
find(text, term) Finds the position of the first occurrence of term in text, or -1 if it could not be found
findLast(text, term) Finds the position of the last occurrence of term in text, or -1 if it could not be found
trim(text), strip(text) Removes any whitespace at the start and end of text
trimLeft(text), stripLeft(text) Removes any whitespace at the start of text
trimRight(text), stripRight(text) Removes any whitespace at the end of text
padLeft(text, minLength) Adds spaces to the left of text until it is at least minLength characters long
padRight(text, minLength) Adds spaces to the right of text until it is at least minLength characters long
padLeft(text, minLength, padText) Adds padText to the left of text until it is at least minLength characters long
padRight(text, minLength, padText) Adds padText to the right of text until it is at least minLength characters long

Adding comments to your formulas

You can use comments to explain your formulas to other users.

Comment Syntax:

  • Comments are started by a hash #, and extend to the end of that line. Anything written within a comment will be ignored in the formula.
  • Example:     

    # Comments can appear on their own line.     
    a = v1 * 2;     
    res = a + sin(v1); # Or at the end of other lines.