Columnal Documentation: Table of Contents

Types

Boolean typeText typeNumber typeDate typeDateYM typeTime typeDateTime typeDateTimeZoned typeList typeTagged typeUnit typetypevar type

Literals

dateym{..} literaldate{..} literaltime{..} literaldatetime{..} literaldatetimezoned{..} literaltype{..} literal

Syntax

If Then ElseMatchDefineFunctionImplicit Function

Operators

Operator +Operator -Operator ;Operator ±Operator <>Operator =Operator =~Operator &Operator |Operator <Operator <=Operator /

boolean functions

notxor

comparison functions

minimummaximumminimum indexmaximum index

conversion functions

from textfrom text toto textextract numberextract number or none

core functions

as typetype ofconvert unit

datetime functions

date from ymdtime from hmstime from datetimetime from datetimezoneddate from datetimedate from datetimezoneddatetime from datetimezoneddateym from ymdateym from datedatetime from dtdatetimezoned from dtzdays betweenadd daysseconds between

list functions

list lengthelementelement orget singlejoin lists

listprocess functions

anyallnonecombineselectapply eachcount wherelist contains

lookup functions

lookuplookup all

math functions

loglog natural

number functions

roundround decimalround significantabsaveragesum

optional functions

get optionalget optional orget optionals from list

text functions

text lengthreplacereplace manyjoin textjoin text withtrimsplit textlower case
Boolean

A Boolean value is one that is either true, or false.

This is a useful concept that crops up in many places. If you want to say "if A then B else C", then A must be a Boolean: true or false.

See Alsooperator &operator |operator =operator <>boolean
Text

A Text value (often also known as a string). Text values are enclosing in double quotes: "like this".

Text values are represented using Unicode.

See Alsooperator ;text
Number

A number (either whole number or not), for example 6 or -7.2

A number may have an attached unit (such as years, or metres per second), written in curly brackets afterwards. To add or subtract two numbers, they must have the same units. Units are tracked through multiplication and division.

Numbers are stored as decimals and addition and subtraction will use decimal (not binary) arithmetic. No guarantees on precision are given on the type or on other operations.

See AlsoUnitoperator +operator *operator /operator ^numbermath
Date

A date, for example 29 August 2008. Only valid dates are allowed (for example, 30 February 2000 is not allowed).

Dates can be entered in expressions using the date{} syntax, for example: date{29 August 2008}.

To avoid confusion over whether the day or month comes first, this type converts to text using the ISO 8601 standard of YYYY-MM-DD, so 29 August 2008 is written 2008-08-29.

This type has no time or timezone attached.

See AlsoDateTimedatetimedate{..}
DateYM

A year and month, for example August 2014, usually written 2014-08. Only valid items are allowed.

This is useful for things like tracking sales for a particular month.

See AlsoDatedateym{..}
Time

A time of day, for example 10:08. Only valid times are allowed (e.g. 24:02 is not allowed).

The time can feature seconds, including fractional seconds, e.g. 15:04:11.27441.

This type converts to text using the 24-hour clock.

This type has no date or timezone attached.

See AlsoDateDateTimedatetimetime{..}
DateTime

A date and time of day, for example 2000-01-01 12:34:56.7890

This type has no timezone attached.

See AlsoDateTimeZoneddatetimedatetime{..}
DateTimeZoned

A date and time of day, with an attached time zone.

Three letter time zones (e.g. EDT) are avoided for two reasons. Firstly, some of the abbreviations overlap. Secondly, most of these time zones only exist part of the year, meaning that times like 2000-01-01 00:00 BST (British Summer Time) are nonsense, as BST does not exist in January.

Instead, time zones are specified either using continent/major_city notation for geography-based times, or (best of all) an offset from UTC.

See AlsoDateTimedatetimedatetimezoned{..}
List

A list of items of the same type, written as square brackets around the type, for example [Text] is a list of text values.

If you want a list with items of multiple types, use a tagged type.

See Alsolistlistprocess
Tagged

A tagged type has one or more tags, each with an optional inner type. A value is one of these tags, with a value if needed.

For example, to represent pricing at a theme park, you might have a tagged type with tags Child, Adult, Senior.

Some tagged types may be flexible in their inner types, in which case they are represented using a type variable. For example, the concept of an optional value occurs often: a value may be missing because a survey respondent didn't fill it in, because some sensor data is missing, because a price is unknown, or because the value is not applicable.

Rather than have a separate tagged type for each inner type, e.g. OptionalBoolean, OptionalText, and so on, we have one Optional type that takes a type variable, so we can have Optional(Boolean) or Optional(Text), and so on.

See Alsomaybe
Unit

Units of measurement include things like seconds, kilometres, dollars, users, and so on.

Units are optional -- you can choose to use them or not. They can be useful for two main reasons. First, they provide a reminder of how something is measured -- if you have a column called distance, it is useful to specify whether than is in miles or kilometres. Second, the units are tracked to make sure you do not add two mismatching units (e.g. adding a number in metres per second to a number in miles per hour) and allow you to convert between related units (e.g. inches to metres).

See AlsoasUnit(..)
typevar

Some functions can take a variety of types. For example, the sort function can take a list of any sortable type: a list of numbers, a list of text values, a list of pairs of dates and numbers, and so on. To denote this, we give the type a generic name, usually t, or a, b, c, which is shown in italics in this interface. You can read this as "any type, which we name t".

Sometimes a type may have constraints on it. These constraints are written above the function's type.

dateym{...}

Creates a year-month value -- this is a date without a day of the month. You can use month numbers or English month names.

ExamplesClick an example to insert it
dateym{April 2003}dateym from ym(2003{year}, 4{month})
dateym{11/2010}dateym from ym(2010{year}, 11{month})
dateym{Sep 1921}dateym from ym(1921{year}, 9{month})
date{...}

Creates a date value. The format is quite flexible, the main issue is avoid ambiguous dates. Dates like 4/8/2000 could be interpreted as 4th August or 8th April. This issue can be avoided in several ways: you can use the ISO format (e.g. date{2010-04-05} is 5th April), or you can use an (English) month name, e.g. date{5 April 2010}

ExamplesClick an example to insert it
date{10 April 2003}date from ymd(2003{year}, 4{month}, 10{day})
date{2010-11-28}date from ymd(2010{year}, 11{month}, 28{day})
date{Sep 3 1921}date from ymd(1921{year}, 9{month}, 3{day})
time{...}

Creates a time value, representing a time of day. You can use the 24-hour clock or AM/PM. Fractional seconds are allowed.

ExamplesClick an example to insert it
time{08:57}time from hms(8{hour}, 57{minute}, 0{s})
time{9:12:03 PM}time from hms(21{hour}, 12{minute}, 3{s})
time{23:59:59.999999}time from hms(23{hour}, 59{minute}, 59.999999{s})
datetime{...}

Creates a date-time value, with a date and time of day on that date.

The date part of the format is quite flexible, the main issue is avoid ambiguous dates. Dates like 4/8/2000 could be interpreted as 4th August or 8th April. This issue can be avoided in several ways: you can use the ISO format (e.g. datetime{2010-04-05 12:34} is 5th April), or you can use a month name, e.g. date{5 April 2010 4:56}

For the time part, you can use the 24-hour clock or AM/PM. Fractional seconds are allowed.

ExamplesClick an example to insert it
datetime{10 April 2003 08:57}datetime from dt(date from ymd(2003{year}, 4{month}, 10{day}), time from hms(8{hour}, 57{minute}, 0{s}))
datetime{2010-11-28 9:12:03 PM}datetime from dt(date from ymd(2010{year}, 11{month}, 28{day}), time from hms(21{hour}, 12{minute}, 3{s}))
datetime{Sep 3 1921 23:59:59.999999}datetime from dt(date from ymd(1921{year}, 9{month}, 3{day}), time from hms(23{hour}, 59{minute}, 59.999999{s}))
datetimezoned{...}

Creates a zoned date-time value, with a date and time of day on that date, and a time zone.

The date part of the format is quite flexible, the main issue is avoid ambiguous dates. Dates like 4/8/2000 could be interpreted as 4th August or 8th April. This issue can be avoided in several ways: you can use the ISO format (e.g. datetime{2010-04-05 12:34} is 5th April), or you can use a month name, e.g. date{5 April 2010 4:56}

For the time part, you can use the 24-hour clock or AM/PM. Fractional seconds are allowed.

Time zones can be specified in two ways. The simplest is as an offset from UTC, e.g. +10:00 is ten hours ahead of UTC, -01:30 is one and a half hours behind. The alternative is to use a continent/city format, e.g. Europe/London. The city format will use the UTC offset that the official local time used on the given date, so datetimezoned{2019-01-14 03:40 Europe/London} is 03:40 UTC as London operates on UTC in the winter, but datetimezoned{2019-05-14 03:40 Europe/London} is 04:40 UTC because London is one hour ahead during the summer.

ExamplesClick an example to insert it
datetimezoned{10 April 2003 08:57+00:00}datetimezoned from dtz(date from ymd(2003{year}, 4{month}, 10{day}), time from hms(8{hour}, 57{minute}, 0{s}), "+00:00")
datetimezoned{2010-11-28 9:12:03 PM Asia/Jakarta}datetimezoned from dtz(date from ymd(2010{year}, 11{month}, 28{day}), time from hms(21{hour}, 12{minute}, 3{s}), "Asia/Jakarta")
datetimezoned{Sep 3 1921 23:59:59.999999 Africa/Khartoum}datetimezoned from dtz(date from ymd(1921{year}, 9{month}, 3{day}), time from hms(23{hour}, 59{minute}, 59.999999{s}), "Africa/Khartoum")
type{...}

Creates a type value. Type values are used for functions like from text to(..), and as tsype(..), to specify a destination type.

ExamplesClick an example to insert it
from text to(type{Boolean}, "true")true
from text to(type{Number{minute}}, "93")93{minute}
from text to(type{@apply Optional(Date)}, "None")Optional\None
@if condition @then value if true @else value if false @endif For any types t @if Boolean @then t @else t @endif

Chooses between two expressions depending on whether the condition is true or false. For example, @if score >= 0 @then "Positive" @else "Negative" @endif would check whether the score expression is greater than or equal to zero. If it was, the result would be the "Positive" text; otherwise it would be the "Negative" text.

ExamplesClick an example to insert it
@if 5 >= 0 @then "Positive" @else "Negative" @endif"Positive"
@if "A" = "B" @then 1 @else 0 @endif0
@match expression @case pattern @given guard @orcase another pattern @given another guard @then value if matches @endmatch For any types c, t @match c @case c @given Boolean @orcase c @given Boolean @then t @endmatch

Matches against several possible alternatives. For example, @match desc @case "full" @orcase "max" @then 1.0 @case _ @then 0.5 @endmatch checks if desc is equal to "full" or "max". If so, the result is 1.0, otherwise (the case underscore matches anything else) the result is 0.5.

ExamplesClick an example to insert it
@match "B" @case "A" @then true @case "B" @then false @endmatchfalse
@match "Hello There" @case "Bye" @then "Leaving" @case "Hello" ; _ @then "Arriving" @endmatch"Arriving"
@match 32 @case 0 @then "Zero" @case n @given n > 0 @then "Positive" @endmatch"Positive"
@define definition @then expression @enddefine @define _ @then t @enddefine

Defines a name for a value, to allow later use. Often you may find yourself repeating the same piece of code, which is both tiresome to enter, and awkward when you need to edit it in multiple places later. By defining a name for the shared code, it's easier to reference and edit.

Another use for definitions is to do pattern matching, especially for things like optional types; for example @define Is(x) = Column with optional type @then x @enddefine

ExamplesClick an example to insert it
@define feet mm = convert unit(unit{mm},1{foot}) @then (6 * feet mm) * (4 * feet mm) @enddefine2229672.96{mm^2}
@define sq = @function(x) @then x*x @endfunction, x = sq(256) @then x - 1 @enddefine65535
@define distance :: type{Number{m}}, distance = from text("23") @then distance * 2 @enddefine46{m}
@function parameters @then body @endfunction @function a @then b @endfunction

Makes a function. The comma-separated list of parameters goes after the function\\keyword, and the body of the function goes after the @then. Functions are very often used with @define, which allows you to name and re-use the function.

If the function you need is very simple and uses its parameter once, you may find the quest syntax useful.

ExamplesClick an example to insert it
@define low = @function(a, b) @then @if a < b @then a @else b @endif @endfunction @then low(3, -5) @enddefine-5
apply each([2,3,4], @function(x) @then x * x @endfunction)[4,9,16]
? ?

Sometimes you want a function that does something very simple, like adding one to a number. The full function syntax is cumbersome for this. Instead you can write (? + 1), which is a function with a single parameter that adds one to get the result. If you have multiple question marks, e.g. ? * ?, the function has as many parameters as there are question marks.

The function extends to the single expression that the question mark(s) appear in. So 0 <= ? < ? <= 1 takes two parameters and checks that the first is less than the second, and they are both between zero and one. The expression (? + 1) * 2 is a type error because you can't multiply a function like two (use the full function syntax instead).

ExamplesClick an example to insert it
apply each([3,7,-4], (? + 1))[4,8,-3]
combine([2, 4, 3], (? * ?))24
group count

A count of the number of items that collapsed into the destination row. The aggregate expression can either be calculated once for the whole source table (in which case this variable is the length of the source table) or split by row values.

For example, if you split by a boolean column Winning, there will be one aggregated row for all the rows in the source table where Winning was true -- and there, group count will be the number of rows in the source table where Winning was true.

If you want a frequency table, using group count by itself will produce the right result.

row

The number of the current row. The first row is 1, the second row is 2 and so on.

operator +operator -(Number{u}+Number{u}-...)Number{u}

Adds or subtracts numbers. All the numbers must have no units, or the same units.

Plus cannot be used for non-numbers. Some other types:

ExamplesClick an example to insert it
1 + 2 + 36
10 - 2 - 3 + 16
operator ;(Text;Text...)Text

Joins text items together in order.

This operator can also be used in match expressions, usually to match and remove items from the beginning or end of a text item (see examples, below).

ExamplesClick an example to insert it
"a" ; "b" ; "c""abc"
@match "T-2000" @case "T-" ; y @then from text(y) @endmatch2000
operator ±(Number{uNumber{u})Number{u}

This is only valid in match cases or the right-hand side of the operator ~ operator.

It specifies a pattern that a number must be close to the left-hand operator, within the tolerance specified by the right-hand side.

ExamplesClick an example to insert it
(1 / 3) =~ (0.333 ± 0.001)trueDeal with inexact fractions by using a tolerance
operator <>(t<>t)Boolean

Tests whether two values are not-equal

ExamplesClick an example to insert it
3 <> 6true
operator =operator =~(t=t=~...)Boolean

Checks that all the items are equal.

Note that you can compare more than two items at once. The equals expression is true only if all the items are equal to each other.

Checks if the value on the left, matches the pattern on the right.

If there are no special patterns on the right-hand side (e.g. _, operator ±, new variables) then it is equivalent to operator =.

If you do declare a variable in the right-hand side, the variable will not be accessible outside the pattern. For that, use the full match expression.

ExamplesClick an example to insert it
1 = 2false
from text("1") = 1true
(2/2)=1=(2-1)true
"$50" =~ ("$" ; _)trueCheck if text starts with a dollar sign
50.0006 =~ (50 ± 0.01)trueCheck if number is close to another
["Hi", "there"] =~ ["", _]falseCheck if a list has two items, and the first is an empty text value
(low:2, high:4) =~ (low:x, high:(x + 2))trueCheck if the high item is exactly 2 higher than the low item
["Bye", "Bye"] =~ [s, s]trueCheck if a list has two values, and they are the same
operator &(Boolean&Boolean...)Boolean

Checks if all items are true.

The items are checked left-to-right and are not evaluated beyond the first false item. See the first example for how this is useful.

ExamplesClick an example to insert it
(list length([6, 7]) >= 2) & (element([6, 7], 2) = 5)falseWill not cause an error because list size is checked before getting the item.
("a" = "a") & ("a" <> "b")true
true & true & falsefalse
operator |(Boolean|Boolean...)Boolean

Checks if any items is true.

The items are checked left-to-right and are not evaluated beyond the first true item. See the first example for how this is useful.

ExamplesClick an example to insert it
(list length([6, 7]) >= 2) & (element([6, 7], 2) = 5)falseWill not cause an error because list size is checked before getting the item.
("a" = "b") | ("a" <> "b")true
true | true | falsetrue
operator <operator <=(t<t<=...)Boolean

Compares the values.

Note that you can chain multiple less-than and less-than-or-equals in the same expression.

The rules for different types are as follows:

ExamplesClick an example to insert it
1 < abs(-2) < 3true
false < truetrue
"mud" < "muddle"true
as type(type{DateTimeZoned}, from text("2013-05-02 12:00 Europe/London")) < from text("2013-05-02 11:37 America/Toronto")true
Optional\None < Optional\Is(0)true
(a:0, b:5) < (a:1, b:3)true
operator /(Number{u}/Number{v})Number{u/v}

Divides the left side by the right side.

This will produce a decimal component if necessary. If you want integer division, round the result.

The result will have appropriate units, dividing the top units by the bottom units.

ExamplesClick an example to insert it
5 / 22.5
round decimal((100{m} / 9.58{s}), 2)10.44{m/s}
not(bool)negated Type: (Boolean) -> Boolean

Returns the opposite Boolean (given true, returns false; given false, returns true)

ExamplesClick an example to insert it
not(true)false
not(false)true
xor(a, b)xored Type: (Boolean, Boolean) -> Boolean

Returns the exclusive-or of the two items: returns true if exactly one of the items is true, but false if both are true.

ExamplesClick an example to insert it
xor(true, true)false
xor(true, false)true
xor(false, true)true
xor(false, false)false
minimum(items)lowest Type: For any types t where Comparable t([t]) -> t

Gets the lowest item in the list.

Lowest is determined in the same way as operator < -- see the documentation for more details.

ExamplesClick an example to insert it
minimum([54, 7, 82])7
minimum(["cat", "aardvark", "bear"])"aardvark"
minimum([])error
maximum(items)highest Type: For any types t where Comparable t([t]) -> t

Gets the highest item in the list.

Highest is determined in the same way as operator < -- see the documentation for more details.

ExamplesClick an example to insert it
maximum([54, 7, 82])82
maximum(["zebra", "wolf", "vole"])"zebra"
maximum([])error
minimum index(items)index of lowest Type: For any types t where Comparable t([t]) -> Number

Gets the index of the lowest item in the list (where the first item is index 1, the second is index 2 and so on).

Lowest is determined in the same way as operator < -- see the documentation for more details.

ExamplesClick an example to insert it
minimum index([54, 82, -7])3
minimum index(["cat", "aardvark", "bear"])2
minimum index([])error
maximum index(items)index of highest Type: For any types t where Comparable t([t]) -> Number

Gets the index of the highest item in the list (where the first item is index 1, the second is index 2 and so on).

Highest is determined in the same way as operator < -- see the documentation for more details.

ExamplesClick an example to insert it
maximum index([54, 82, -7])2
maximum index(["cat", "aardvark", "bear"])1
maximum index([])error
from text(text)value Type: For any types t where Readable t(Text) -> t

Converts a value from text.

This function works for any readable type (the only non-readable types are function types). If the intended type cannot be inferred automatically, you may need to use the from text to(..) function to specify the type .

Some notes for specific types:

  • For boolean values, lower-case and upper-case are allowed in the names.
  • Dates are read using a variety of formats. Be careful with the DD-MM-YY formats and similar as they are ambiguous against MM-DD-YY. If this is an issue, use the date from string function to specify a format.
  • Numbers must use dot as the decimal separator, commas are not supported.
  • If you want to get a Text out of from text, it must be in quotes.
ExamplesClick an example to insert it
from text("65") > 64true
from text("TRUE") | falsetrue
as type(type{Date}, from text("21 March 2004"))date from ymd(2004{year}, 3{month}, 21{day})
as type(type{[(a: Boolean, b: Number{m})]}, from text("[(a:True, b:0), (a:FALSE, b:-1.6)]"))[(a:true, b:0{m}), (a:false, b:-1.6{m})]
from text to(value type, text)value Type: For any types t where Readable t(@apply Type(t), Text) -> t

Converts a value from text, specifying the target type.

See from text(..) for more details.

ExamplesClick an example to insert it
from text to(type{Date}, "21 March 2004")date from ymd(2004{year}, 3{month}, 21{day})
from text to(type{[(a:Boolean, b:Number{m})]}, "[(a:True, b:0), (a:FALSE, b:-1.6)]")[(a:true, b:0{m}), (a:false, b:-1.6{m})]
to text(value)text Type: For any types t where Showable t(t) -> Text

Converts a value to text.

ExamplesClick an example to insert it
to text(true)"true"
to text(65{m})"65"
extract number(text)number Type: (Text) -> Number

Extracts a number from the given text. If there is no number, or two numbers, gives an error.

The difference between this function and from text(..) is that this function will accept and ignore extra text around the number, and deal with comma separators. So whereas from text will give an error on "P65n" because it's not solely a number, this extract number function will return 65.

The function assumes that commas are thousand separators, and dot is the decimal separator. If you need to convert continental European style numbers where the opposite is true, use the replace many function as shown in the last example.

ExamplesClick an example to insert it
extract number("-34.20m")-34.2
extract number("£17,000,000")17000000
extract number("The 6 cats")6
extract number("2 and 2 makes 4")error
extract number("Two")error
extract number(replace many([(find: ".", replace: ","), (find: ",", replace: ".")], "10.449,99"))10449.99Swaps dots and commas
extract number or none(text)number Type: (Text) -> @apply Optional(Number)

Extracts a number from the given text. If there is no number, or two numbers, gives back None.

The difference between this function and extract number(..) is that this function gives back an Optional(Number), so rather than giving an error when there is no number, it gives back None. Successfully extracted numbers are wrapped in the opposing Is constructor; see the optional guide.

ExamplesClick an example to insert it
extract number or none("-34.20m")Optional\Is(-34.2)
extract number or none("£17,000,000")Optional\Is(17000000)
extract number or none("The 6 cats")Optional\Is(6)
extract number or none("2 and 2 makes 4")Optional\None
extract number or none("Two")Optional\None
as type(type, value)value Type: For any types t(@apply Type(t), t) -> t

Clarifies the type of a value.

No processing is done on the value; this function is only for specifying a type when it is ambiguous and you get a type error. For conversions, see functions like conversion:from text(..).

ExamplesClick an example to insert it
as type(type{Boolean}, from text("True"))trueSpecifies that the destination type of the conversion from text is Boolean.
as type(type{[Date]}, [])[]Specifies that the given empty list is a list of dates.
type of(value)type Type: For any types t(t) -> @apply Type(t)

Gets the type of the given value.

ExamplesClick an example to insert it
as type(type of([3]), from text("[1, 2, 5]"))[1, 2, 5]
type of([(a:true, b:6)])type{[(a:Boolean, b:Number)]}
type of((t: time{00:00}, s: 21{s}))type{(t: Time, s: Number{s})}
convert unit(target unit, source number)target number Type: For any units u, v(@apply Unit(u), Number{v}) -> Number{u}

Converts a number from one unit to another (if possible).

Unit conversion is only available for exact conversions (e.g. metres to centimetres), not for approximate or changing conversions (e.g. euros to dollars).

Attempting to convert between unrelated units (e.g. metres to seconds) will result in an error. This includes converting to/from plain numbers. If you want to add a unit to a number multiply by 1 of that unit (for example, duration * 1{s}), if you want to remove a unit then divide by 1 of that unit (for example, distance / 1{m}).

ExamplesClick an example to insert it
convert unit(unit{cm}, 1{inch})2.54{cm}Convert one inch into centimetres
convert unit(unit{l}, 30{cm} * 20{cm} * 10{cm})6{l}Convert a volume in cubic centimetres into litres
convert unit(unit{mile/hour}, (100{m} / 9.58{s}))23.350{mile/hour} ± 0.001{mile/hour}Convert speed running 100 metres in 9.58 seconds into miles per hour
convert unit(unit{m^2}, 10{m})errorTry to convert between unrelated or impossible units (here: length and area) gives an error.
date from ymd(year, month, day)date Type: (Number{year}, Number{month}, Number{day}) -> Date

Creates a date from the given year, month and day.

Months and days start at 1: 1{month} is January, and 1{day} is the first day of the month.

ExamplesClick an example to insert it
date from ymd(1998{year}, 07{month}, 10{day})date{1998-07-10}
date from ymd(2004{year}, 08{month}, 06{day})from text("6 August 2004")
time from hms(hour, minute, second)time Type: (Number{hour}, Number{minute}, Number{s}) -> Time

Creates a time-of-day (or clock time) from the given hours, minutes and seconds.

ExamplesClick an example to insert it
time from hms(13{hour}, 31{minute}, 0{s})time{13:31:00}
time from datetime(datetime)time Type: (DateTime) -> Time

Gets the Time part of a DateTime.

ExamplesClick an example to insert it
time from datetime(datetime{2001-03-04 06:54})time{06:54}
time from datetimezoned(datetimezoned)time Type: (DateTimeZoned) -> Time

Gets the Time part of a DateTimeZoned.

No timezone conversions are performed, this just takes the time part, ignoring the date and the zone.

ExamplesClick an example to insert it
time from datetimezoned(datetimezoned{2001-03-04 06:54 UTC})from text("06:54")
date from datetime(datetime)date Type: (DateTime) -> Date

Gets the Date part of a DateTime.

ExamplesClick an example to insert it
date from datetime(datetime{2001-03-04 06:54})date{2001-03-04}
date from datetimezoned(datetimezoned)date Type: (DateTimeZoned) -> Date

Gets the Date part of a DateTimeZoned.

No timezone conversions are performed, this just takes the time part, ignoring the date and the zone.

ExamplesClick an example to insert it
date from datetimezoned(datetimezoned{2001-03-04 06:54 UTC})date{2001-03-04}
datetime from datetimezoned(datetimezoned)datetime Type: (DateTimeZoned) -> DateTime

Gets the DateTime part of a DateTimeZoned.

No timezone conversions are performed, this just takes the DateTime part, ignoring the zone.

ExamplesClick an example to insert it
datetime from datetimezoned(datetimezoned{2001-03-04 06:54 UTC})datetime{2001-03-04 06:54}
dateym from ym(year, month)dateym Type: (Number{year}, Number{month}) -> DateYM

Creates a year-month value from the given year and month.

ExamplesClick an example to insert it
dateym from ym(1998{year}, 07{month})dateym{1998-07}
dateym from ym(2004{year}, 08{month})dateym{2004-08}
dateym from date(date)dateym Type: (Date) -> DateYM

Gets the year and month part of a Date as a DateYM.

ExamplesClick an example to insert it
dateym from date(from text("2001-03-04"))from text("2001-03")
datetime from dt(date, time)datetime Type: (Date, Time) -> DateTime

Creates a DateTime from the given Date and Time.

ExamplesClick an example to insert it
datetime from dt(from text("2001-02-03"), from text("04:56"))from text("2001-02-03 04:56")
datetimezoned from dtz(date, time, zone)datetimezoned Type: (Date, Time, Text) -> DateTimeZoned

Creates a datetime with a time zone from the given Date, Time and time zone.

Note that because many of the three-letter abbreviations for time zones overlap, they are not supported here, and you must spell out the full name.

ExamplesClick an example to insert it
datetimezoned from dtz(from text("2001-02-03"), from text("04:56"), "America/Vancouver")from text("2001-02-03 04:56 America/Vancouver")
days between(start, end)days Type: (Date, Date) -> Number{day}

Calculates the number of days between two dates.

If the first date is before the second date, the returned number is positive. If the first date is after the second date, the returned number will be negative.

ExamplesClick an example to insert it
days between(from text("7 May 2003"), from text("16 October 2004"))528{day}
add days(date, days to add)result date Type: (Date, Number{day}) -> Date

Adds the given number of the days to the date.

ExamplesClick an example to insert it
add days(date{2002-11-01}, 28{day})date{2002-11-29}
add days(date{2000-01-01}, 0.5{day})error
seconds between(start, end)seconds Type: (Time, Time) -> Number{s}

Calculates the number of seconds between two times.

If the first time is before the second time, the returned number is positive. If the first time is after the second time, the returned number will be negative.

This function does not consider wrap-around. That is, 00:30 is considered to be 23 hours before 23:30, not one hour after.

ExamplesClick an example to insert it
seconds between(time{00:05:08}, time{00:05:29})21{s}
list length(list)length Type: For any types t([t]) -> Number

Gets the number of elements in the given list.

ExamplesClick an example to insert it
list length(["a", "word", "list"])3
list length([2, 4, 6, 8])4
list length([])0
element(list, position)list item Type: For any types t([t], Number) -> t

Gets the list element at the given position (first position is 1).

ExamplesClick an example to insert it
element(["x", "y", "z"], 1)"x"
element([2, 4, 6, 8], 4)8
element([], 1)error
element([true, false], 0)error
element or(list, position, default)list item Type: For any types t([t], Number, t) -> t

If the number is within the bounds of the list, gives back the element at that position (e.g. 3 gets the 3rd item in the list). But if the number is 0 or less, or greater than the list size, the third parameter will be returned instead.

This is particularly useful for accessing the previous row of a column and avoiding an error on the first element, for example element or(table\\Table#Totals, row - 1, 0) in a Calculate will give the value from the Totals column in the previous row, or 0 if this is the first row of the table.

ExamplesClick an example to insert it
element or(["a", "b"], 0, "z")"z"
element or([7, 8, 9], 3, 0)9
element or([true, true, true], 5, false)false
get single(list)single item Type: For any types t([t]) -> t

If this list has exactly one element, returns it. Otherwise gives an error.

This is useful if you have a table where you store single values. The column is a list, so you need to use a function, like single, to fetch out the one item in it.

ExamplesClick an example to insert it
get single([65])65
get single([])error
get single([1, 2])error
join lists(list of lists)joined lists Type: For any types t([[t]]) -> [t]

Takes a list of lists, and joins them into one list by joining them end-to-end.

Each item in the passed list must be a list. To include single items, wrap them in square brackets [].

ExamplesClick an example to insert it
join lists([[0, 1], [3, 4], [7]])[0, 1, 3, 4, 7]
join lists([[true], [], [false]])[true, false]
join lists([])[]
any(list, test)any pass test Type: For any types t([t], (t) -> Boolean) -> Boolean

Returns true if the given function returns true for any item in the list.

If the list is empty, returns false.

ExamplesClick an example to insert it
any(["a", "b", "c"], (? = "b"))true
any([0, 1, 2, 3], (? > 5))false
any([], (? = 0))false
all(list, test)all pass test Type: For any types t([t], (t) -> Boolean) -> Boolean

Returns true if the given function returns true for all items in the list.

If the list is empty, returns true.

ExamplesClick an example to insert it
all([0, 1, 2, 3], (? < 5))true
all(["a", "b", "c"], (? = "b"))false
all([], (? = 0))true
none(list, test)none pass test Type: For any types t([t], (t) -> Boolean) -> Boolean

Returns true if the given function returns true for no items in the list.

If the list is empty, returns true.

ExamplesClick an example to insert it
none([0, 1, 2, 3], (? > 5))true
none(["a", "b", "c"], (? = "b"))false
none([], (? = 0))true
combine(items, combining function)combined Type: For any types t([t], (t, t) -> t) -> t

Collapses the list into a single item by combining the elements using the given function.

ExamplesClick an example to insert it
combine([1, 2, 3], (? + ?))6
combine(["a", "b", "c"], (? ; ?))"abc"
combine([true, false, false, true], (? | ?))true
combine([], (? + ?))error
select(list, test)items passing test Type: For any types t([t], (t) -> Boolean) -> [t]

Returns a new list containing all the items in the list for which the function returns true.

ExamplesClick an example to insert it
select([3, 8, 4, 6, 2], (? >= 5))[8, 6]
select([], (? = 0))[]
apply each(list, transform function)transformed list Type: For any types before, after([before], (before) -> after) -> [after]

Applies the function to each item in the list, and returns the resulting items.

The order of the items in the new list will correspond to the original order.

ExamplesClick an example to insert it
apply each([3, 6, 9], (? + 1))[4, 7, 10]
apply each([-3, 4, -7], abs)[3, 4, 7]
count where(list, test)count Type: For any types t([t], (t) -> Boolean) -> Number

Counts the number of items in the list where the given test function returns true.

ExamplesClick an example to insert it
count where([11, -2, 14, 4, 0], (? > 10))2
count where(["Hi", "Hello"], (? = "Bye"))0
list contains(list, item)is in list Type: For any types t where Equatable t([t], t) -> Boolean

Checks if the item is in the list.

ExamplesClick an example to insert it
list contains([1, 2, 3], 2)true
list contains(["a", "b", "c"], "d")false
lookup(keys, key, values)value Type: For any types a, b where Equatable a([a], a, [b]) -> b

Given a list, finds a matching item, and returns the corresponding value from the other list.

This is useful for looking up one column via the value of another, e.g. lookup(Column A, value in A, Column B)

Gives an error if there are zero or multiple matches in the left side.

ExamplesClick an example to insert it
lookup([4, 5, 6], 6, ["a", "b", "c"])"c"
lookup([1, 1, 2, 3], 1, ["a", "b", "c", "d"])error
lookup all(keys, key, values)matching values Type: For any types a, b where Equatable a([a], a, [b]) -> [b]

Given two lists, finds all the items with a matching value in left list, and returns the right list values for each.

This is useful for looking up one column via the value of another, e.g. lookup all(Column A, value in A, Column B)

ExamplesClick an example to insert it
lookup all([1, 2, 1, 3], 1, ["a", "b", "c", "d"])["a", "c"]
log(base raised to x, base)x Type: For any units u(Number{u}, Number) -> Number

Takes the logarithm of the first number, using the second number as the base.

ExamplesClick an example to insert it
log(100, 10)2
log(256, 2)8
log(53252, 10)4.726 ± 0.001
log natural(e raised to x)x Type: For any units u(Number{u}) -> Number

Takes the natural logarithm (i.e. the logarithm with base e).

ExamplesClick an example to insert it
log natural(53252)10.883 ± 0.001
log natural(1)0
log natural(0)error
round(decimal)rounded Type: For any units u(Number{u}) -> Number{u}

Rounds the given number to the nearest whole number (integer).

Numbers that end in .5 (which are halfway between two whole numbers) are rounded towards the nearest even number, sometimes known as banker's rounding.

ExamplesClick an example to insert it
round(0.333)0
round(2)2
round(0.5)1
round(1.5)2
round decimal(decimal, decimal places)rounded Type: For any units u(Number{u}, Number) -> Number{u}

Rounds the number to the given number of decimal places (digits after the decimal point).

ExamplesClick an example to insert it
round decimal(3.1415927, 2)3.14
round significant(decimal, significant figures)rounded Type: For any units u(Number{u}, Number) -> Number{u}

Rounds the number to the given number of significant figures (digits at the beginning of the number).

ExamplesClick an example to insert it
round significant(3.1415927, 2)3.1
round significant(1234567, 4)1235000
abs(original)positive Type: For any units u(Number{u}) -> Number{u}

Makes a number positive by discarding the minus sign.

ExamplesClick an example to insert it
abs(-3.6)3.6
abs(5)5
abs(0)0
average(numbers)average Type: For any units u([Number{u}]) -> Number{u}

Calculates the average of a list of numbers.

ExamplesClick an example to insert it
average([1, 1.5, 5])2.5
average([33.4])33.4
average([])error
sum(numbers)total Type: For any units u([Number{u}]) -> Number{u}

Calculates the sum (total) of a list of numbers.

ExamplesClick an example to insert it
sum([1, 1.5, 5])7.5
sum([33.4])33.4
sum([])0
get optional(optional value)inner value Type: For any types t(@apply Optional(t)) -> t

Gets the value from inside an optional value, if it is present. If the value is blank, gives an error. If you want to supply a value in this case, use get optional or(..).

ExamplesClick an example to insert it
get optional(Optional\Is(17))17
get optional(Optional\None)error
get optional or(optional value, default value)result Type: For any types t(@apply Optional(t), t) -> t

Gets the value from inside an optional value, if it is present. If the value is blank, returns the second parameter instead.

ExamplesClick an example to insert it
get optional or(Optional\Is(17), 12)17
get optional or(Optional\None, 12)12
get optionals from list(list of optionals)present values Type: For any types t([@apply Optional(t)]) -> [t]

Gets all the present values from the list of optionals, in the same order as the original list. All None items are discarded.

ExamplesClick an example to insert it
get optionals from list([Optional\Is(17), Optional\None, Optional\Is(42), Optional\None])[17, 42]
get optionals from list([Optional\None])[]
text length(text)length Type: (Text) -> Number

Gets the length of the given text.

Technical note: this is actually the number of Unicode codepoints in the text. For English text without accents, one character is one codepoint. If you have complex characters (e.g. flags) in your text, what looks like one character may be made up of multiple codepoints.

ExamplesClick an example to insert it
text length("hello")5
text length("a b.")4
text length("")0
replace(to replace, replacement, source)with replaced Type: (Text, Text, Text) -> Text

Replaces all occurrences of the first text with the second text, within the third text

If the first text is empty, no replacement is performed. If any occurrences overlap, the earlier part is replaced first.

ExamplesClick an example to insert it
replace("at", "og", "The cat in the hat")"The cog in the hog"
replace("oo", "a", "Mooo!")"Mao!"
replace("", "x", "Orange")"Orange"
replace many(replacements, source)with replaced Type: ([(find: Text, replace: Text)], Text) -> Text

Given a list of find/replace items, does all the find and replacements in the second parameter.

If the find text is empty, no replacement is performed. If any find occurrences overlap, the earliest matching item in the find/replace is used.

ExamplesClick an example to insert it
replace many([(find: "at", replace: "og"), (find: "the", replace: "a")], "the cat in the hat")"a cog in a hog"
replace many([(find: "ooo", replace: "eow"), (find: "oo", replace: "eh")], "Mooo moo mooo!")"Meow meh meow!"
replace many([(find: "apple", replace: "pear")], "orange")"orange"
replace many([], "original")"original"
join text(list of text)joined Type: ([Text]) -> Text

Joins a list of text values together as one text item.

If you want to add a separator between each text item, use join text with(..) instead.

ExamplesClick an example to insert it
join text(["a", "b", "c"])"abc"
join text(["Hello", " ", "Moon"])"Hello Moon"
join text([])""
join text with(list of text, separator)joined Type: ([Text], Text) -> Text

Joins a list of text values together as one text item, inserting a separator between each.

ExamplesClick an example to insert it
join text with(["a", "b", "c"], "+")"a+b+c"
join text with(["Blanks", "", "Count"], ";")"Blanks;;Count"
join text with(["Hello", " ", "Moon"], "-")"Hello- -Moon"
join text with(["Hi"], ",")"Hi"
join text with([], ",")""
trim(original)trimmed Type: (Text) -> Text

Removes white space from beginning and end of the text.

ExamplesClick an example to insert it
trim("No spaces either side.")"No spaces either side."
trim(" Several spaces ")"Several spaces"
trim("^t Tabs and newlines also removed.^n")"Tabs and newlines also removed."
split text(original, separator)split Type: (Text, Text) -> [Text]

Splits a text item into a list of smaller text items by dividing at the places where the separator occurs.

The returned list does not feature the separators.

ExamplesClick an example to insert it
split text("The quick brown fox", " ")["The", "quick", "brown", "fox"]
split text("6:08:32", ":")["6", "08", "32"]
split text("*abc*", "*")["", "abc", ""]
split text("Hello", "")["H", "e", "l", "l", "o"]
lower case(text)lower case text Type: (Text) -> Text

Changes all characters in the text value into lower-case versions, where possible.

ExamplesClick an example to insert it
lower case("HELLO")"hello"
lower case("Paris")"paris"
lower case("12345")"12345"