Table functions

These functions read and reshape tables. Rows and columns are 1-indexed meaning the first row and first column are index 1.

[3; 4; 5]                              # a one-dimensional table (3 rows)
[[3; 100]; [4; 200]; [5; 300]]         # a two-dimensional table (3 rows, 2 columns)

Size

Length [length]

length(table)

The number of rows.

length([3; 4; 5])                  => 3
length([[3;10]; [4;20]; [5;30]])   => 3
length([])                         => 0

Width [width]

width(table)

The number of columns.

width([3; 4; 5])                   => 1
width([[3;10]; [4;20]; [5;30]])    => 2
width([])                          => 0

Access

Item [item]

item(table; index)
item(table; row; column)

A single value: from the first column at index, or at the intersection of row and column. An index outside the table returns an Invalid dimensions error.

item([3; 4; 5]; 3)                     => 5
item([[3;10]; [4;20]; [5;30]]; 3; 2)   => 30

Row [row, rows]

row(table; index)
rows(table; first; last)

A single row at index, or the rows from first to last inclusive. An index out of range returns an Invalid dimensions error; last less than first returns a Parameter out of range error.

row([[3;10;100]; [4;20;200]; [5;30;300]]; 1)         => [3; 10; 100]
rows([[3;10;100]; [4;20;200]; [5;30;300]]; 1; 2)     => [[3;10;100]; [4;20;200]]

Column [column, columns]

column(table; index)
columns(table; first; last)

A single column at index, or the columns from first to last inclusive. Bounds behave as for row.

column([[3;10;100]; [4;20;200]; [5;30;300]]; 1)      => [3; 4; 5]
columns([[3;10;100]; [4;20;200]; [5;30;300]]; 1; 2)  => [[3;10]; [4;20]; [5;30]]

Subset [subset]

subset(table; firstRow; lastRow; firstColumn; lastColumn)

The rectangular region between the given rows and columns, inclusive.

subset([[3;10;100]; [4;20;200]; [5;30;300]]; 2; 3; 1; 2)   => [[4;20]; [5;30]]

Search

lookup, vlookup, and rlookup all search one column of a table for value and then return something from the first matching row — its position, a value from another column, or the whole row.

Common parameters

All three share the same three arguments:

value — the value to search for.
the searched column — which column to look in. This is the column argument (called inputColumn in vlookup). When it is omitted, the first column is searched.
method — how the match is made: 0 (the default) finds an exact match, -1 finds the largest value ≤ value, and 1 finds the smallest value ≥ value. An exact-match search may be in any order; the -1 and 1 methods require the searched column to be sorted in ascending order.

The first row and column are index 1.

Lookup [lookup]

lookup(table; value)
lookup(table; value; column)
lookup(table; value; column; method)

The row index (1-based) of the first row whose searched column matches value, or 0 when nothing matches — use it to find where a value sits. column selects the column to search (the first column when omitted); method controls how the match is made. Shares the value, searched-column, and method arguments — details.

lookup([[1;50]; [5;45]; [30;40]]; 30)           => 3
lookup([[1;50]; [5;45]; [30;40]]; 45; 2)        => 2
lookup([[1;50]; [5;45]; [30;40]]; 1; 1; 0)      => 1
lookup([[1;50]; [5;45]; [30;40]]; 20; 1; -1)    => 2
lookup([[1;50]; [5;45]; [30;40]]; 20; 1; 1)     => 3

Value lookup [vlookup]

vlookup(table; value; returnColumn)
vlookup(table; value; inputColumn; returnColumn)
vlookup(table; value; inputColumn; returnColumn; method)

The value in returnColumn from the first matching row — a spreadsheet-style lookup (find a row by one column, read a cell from another). With three arguments, value is searched in the first column; with four or more, inputColumn is the column searched and returnColumn the column read from. method controls how the match is made. No match yields a Parameter out of range error. Shares the value, searched-column, and method arguments — details.

vlookup([[1;50]; [5;45]; [30;40]]; 30; 2)        => 40
vlookup([[1;50]; [5;45]; [30;40]]; 5; 1; 2)      => 45
vlookup([[1;50]; [5;45]; [30;40]]; 5; 1; 2; 0)   => 45
vlookup([[1;50]; [5;45]; [30;40]]; 20; 1; 2; -1) => 45
vlookup([[1;50]; [5;45]; [30;40]]; 20; 1; 2; 1)  => 40

Row lookup [rlookup]

rlookup(table; value)
rlookup(table; value; column)
rlookup(table; value; column; method)

The entire matching row, as a table — use it when you need several values from the matched row at once, rather than its position (lookup) or one cell (vlookup). value is the value to find; column selects the column to search (the first column when omitted); method controls how the match is made. No match yields a Parameter out of range error. Shares the value, searched-column, and method arguments — details.

rlookup([[1;50]; [5;45]; [30;40]]; 30)          => [30; 40]
rlookup([[1;50]; [5;45]; [30;40]]; 45; 2)       => [5; 45]
rlookup([[1;50]; [5;45]; [30;40]]; 5; 1; 0)     => [5; 45]
rlookup([[1;50]; [5;45]; [30;40]]; 20; 1; -1)   => [5; 45]
rlookup([[1;50]; [5;45]; [30;40]]; 20; 1; 1)    => [30; 40]

Reshape

Sort [sort]

sort(table; columns)
sort(table; columns; descending)

Sorts the rows by columns, ascending by default. Pass true for descending. All columns move together. columns is either a single column number or an array of column numbers, each 1-based. An array lists sort keys in precedence order — the first column is the primary key, the next breaks ties among rows equal on the first, and so on; rows equal on every listed column keep their original relative order. A single number is equivalent to a one-element array — 1 and [1] behave the same. descending sets one direction for the whole sort; per-column directions are not supported. A column number below 1 or greater than the table’s column count — passed directly or inside the array — returns Invalid dimensions.

sort([5; 2; 3; 7]; 1)                        => [2; 3; 5; 7]
sort([5; 2; 3; 7]; 1; true)                  => [7; 5; 3; 2]
sort([[5;35]; [2;20]; [3;80]; [7;40]]; 2)    => [[2;20]; [5;35]; [7;40]; [3;80]]

With an array, ties on the primary key fall through to the next column. Here the primary key is column 2, then 3, then 1, then 4:

sort([[9;3;16;1]; [9;6;16;1]; [4;3;16;7]; [9;3;5;1]]; [2;3;1;4])
  => [[9;3;5;1]; [4;3;16;7]; [9;3;16;1]; [9;6;16;1]]
sort([[9;3;16;1]; [9;6;16;1]; [4;3;16;7]; [9;3;5;1]]; [2;3;1;4]; true)
  => [[9;6;16;1]; [9;3;16;1]; [4;3;16;7]; [9;3;5;1]]

Append [append]

append(table1; table2)
append(table1; table2; asColumns)

Joins table2 onto table1 as new rows by default, or as new columns when asColumns is true. Appended tables must have matching dimensions on the joining edge, or Invalid dimensions is returned.

append([3; 4; 5]; [10; 20; 30])         => [3; 4; 5; 10; 20; 30]
append([3; 4; 5]; [10; 20; 30]; true)   => [[3;10]; [4;20]; [5;30]]

Introduction

Quickstart

Concepts

Domain Library

Playground

Authoring Domains

Data Types

Units

Function Library

API Reference

Resources

Size

Length [length]

Width [width]

Access

Item [item]

Row [row, rows]

Column [column, columns]

Subset [subset]

Search

Common parameters

Lookup [lookup]

Value lookup [vlookup]

Row lookup [rlookup]

Reshape

Sort [sort]

Append [append]

​Size

​Length [length]

​Width [width]

​Access

​Item [item]

​Row [row, rows]

​Column [column, columns]

​Subset [subset]

​Search

​Common parameters

​Lookup [lookup]

​Value lookup [vlookup]

​Row lookup [rlookup]

​Reshape

​Sort [sort]

​Append [append]

Size

Length [length]

Width [width]

Access

Item [item]

Row [row, rows]

Column [column, columns]

Subset [subset]

Search

Common parameters

Lookup [lookup]

Value lookup [vlookup]

Row lookup [rlookup]

Reshape

Sort [sort]

Append [append]