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.
A Text value (often also known as a string). Text values are enclosing in double quotes: "like this".
Text values are represented using Unicode.
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.
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.
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.
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.
A date and time of day, for example 2000-01-01 12:34:56.7890
This type has no timezone attached.
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.
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.
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.
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).
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.
Creates a year-month value -- this is a date without a day of the month. You can use month numbers or English month names.
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}
Creates a time value, representing a time of day. You can use the 24-hour clock or AM/PM. Fractional seconds are allowed.
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.
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.
Creates a type value. Type values are used for functions like from text to(..), and as tsype(..), to specify a destination type.
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.
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.
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
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.
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).
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.
The number of the current row. The first row is 1, the second row is 2 and so on.
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:
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).
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.
Tests whether two values are not-equal
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.
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.
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.
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:
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.
Gets the lowest item in the list.
Lowest is determined in the same way as operator < -- see the documentation for more details.
Gets the highest item in the list.
Highest is determined in the same way as operator < -- see the documentation for more details.
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.
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.
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:
Converts a value from text, specifying the target type.
See from text(..) for more details.
Converts a value to text.
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.
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.
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(..).
Gets the type of the given value.
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}).
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.
Creates a time-of-day (or clock time) from the given hours, minutes and seconds.
Gets the Time part of a DateTime.
Gets the Time part of a DateTimeZoned.
No timezone conversions are performed, this just takes the time part, ignoring the date and the zone.
Gets the Date part of a DateTime.
Gets the Date part of a DateTimeZoned.
No timezone conversions are performed, this just takes the time part, ignoring the date and the zone.
Gets the DateTime part of a DateTimeZoned.
No timezone conversions are performed, this just takes the DateTime part, ignoring the zone.
Creates a year-month value from the given year and month.
Gets the year and month part of a Date as a DateYM.
Creates a DateTime from the given Date and Time.
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.
Calculates the number of years 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.
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.
Adds the given number of the days to the date.
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.
Gets the number of elements in the given list.
Gets the list element at the given position (first position is 1).
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.
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.
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 [].
Returns true if the given function returns true for any item in the list.
If the list is empty, returns false.
Returns true if the given function returns true for all items in the list.
If the list is empty, returns true.
Returns true if the given function returns true for no items in the list.
If the list is empty, returns true.
Collapses the list into a single item by combining the elements using the given function.
Returns a new list containing all the items in the list for which the function returns true.
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.
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.
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)
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.
Rounds the number to the given number of decimal places (digits after the decimal point).
Rounds the number to the given number of significant figures (digits at the beginning of the number).
Makes a number positive by discarding the minus sign.
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(..).
Gets the value from inside an optional value, if it is present. If the value is blank, returns the second parameter instead.
Gets all the present values from the list of optionals, in the same order as the original list. All None items are discarded.
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.
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.
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.
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.
Joins a list of text values together as one text item, inserting a separator between each.
Removes white space from beginning and end of the 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.