New variable methods

Category: common

Case (if/else)

Use case to utilize if-then-else logic, assigning values when expressions evaluate to true - learn more >

  • Return type: dynamic

  • Fields:

    • case

      • required: true

      • input type: case

      • allowed type(s): any

    • defaultValue (Value)

      • required: true

      • input type: variable or literal

      • allowed type(s): any

Cast

Converts the type of a variable - learn more >

  • Return type: dynamic

  • Fields:

    • expression (Variable)

      • required: true

      • input type: variable or literal

      • allowed type(s): any

    • castType (New type)

      • required: true

      • input type: enum

      • allowed type(s): any

    • safeCast

      • required: true

      • input type: boolean

Constant

Create a constant value as a variable - learn more >

  • Return type: dynamic

  • Fields:

    • literal

      • required: true

      • input type: literal

      • allowed type(s): any

Category: date

Current date

Returns the current date - learn more >

  • Return type: date

  • Fields:

    • time_zone (UTC (default))

      • required: false

      • input type: enum

      • allowed type(s): any

New date

Constructs a date from a year, month, and day - learn more >

  • Return type: date

  • Fields:

    • day (day)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer

    • month (month)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer

    • year (year)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer

Date add

Add a period of time to a date - learn more >

  • Return type: date

  • Fields:

    • date_expression (date)

      • required: true

      • input type: variable or literal

      • allowed type(s): date

    • integer_expression (number)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer

    • date_part (Date part (e.g., days))

      • required: true

      • input type: enum

      • allowed type(s): any

Date diff

Calculate distance between two dates - learn more >

  • Return type: integer

  • Fields:

    • date_expression (date)

      • required: true

      • input type: variable or literal

      • allowed type(s): date

    • date_expression2 (date)

      • required: true

      • input type: variable or literal

      • allowed type(s): date

    • date_part (Date part (e.g., days))

      • required: true

      • input type: enum

      • allowed type(s): any

Date subtract

Subtract a period of time from a date - learn more >

  • Return type: date

  • Fields:

    • date_expression (date)

      • required: true

      • input type: variable or literal

      • allowed type(s): date

    • integer_expression (number)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer

    • date_part (Date part (e.g., days))

      • required: true

      • input type: enum

      • allowed type(s): any

Date truncate

Truncates a date to the nearest boundary - learn more >

  • Return type: date

  • Fields:

    • date_expression (date)

      • required: true

      • input type: variable or literal

      • allowed type(s): date

    • date_truncate_part (Date part (e.g., month))

      • required: true

      • input type: enum

      • allowed type(s): any

Date extract

Extracts the date part (e.g, month) from a date - learn more >

  • Return type: integer

  • Fields:

    • date_expression (value)

      • required: true

      • input type: variable or literal

      • allowed type(s): date | dateTime | time

    • date_part (Date part (e.g., month))

      • required: true

      • input type: enum

      • allowed type(s): any

Format date

Returns a formatted string from a date - learn more >

  • Return type: string

  • Fields:

    • format_string (format string)

      • required: true

      • input type: literal

      • allowed type(s): string

    • date_expression (date)

      • required: true

      • input type: variable or literal

      • allowed type(s): date

Format strings

Since there are a wide variety of mechanisms for specifying dates, date times, and times, it is common that calendar and temporal data will often be initially imported as a string unless the data complies with the canonical format for the respective type listed above.

However, by using format strings, you can specify how various fields are encoded within your data and convert to the appropriate type.

For example, to convert dates of mm/dd/yy format (common in the U.S.; e.g. 03/22/89 ), we would specify the format string %m/%d/%y . For a DateTime displayed as Mon Oct 17 2016 17:32:56 , we would specify %a %b %d %Y %H:%M:%S

A complete list of format elements and their meaning can be found below:

Format element

Description

%A

The full weekday name.

%a

The abbreviated weekday name.

%B

The full month name.

%b or %h

The abbreviated month name.

%C

The century (a year divided by 100 and truncated to an integer) as a decimal number (00-99).

%c

The date and time representation.

%D

The date in the format %m/%d/%y.

%d

The day of the month as a decimal number (01-31).

%e

The day of month as a decimal number (1-31); single digits are preceded by a space.

%F

The date in the format %Y-%m-%d.

%G

The ISO 8601 year with century as a decimal number. Each ISO year begins on the Monday before the first Thursday of the Gregorian calendar year. Note that %G and %Y may produce different results near Gregorian year boundaries, where the Gregorian year and ISO year can diverge.

%g

The ISO 8601 year without century as a decimal number (00-99). Each ISO year begins on the Monday before the first Thursday of the Gregorian calendar year. Note that %g and %y may produce different results near Gregorian year boundaries, where the Gregorian year and ISO year can diverge.

%H

The hour (24-hour clock) as a decimal number (00-23).

%I

The hour (12-hour clock) as a decimal number (01-12).

%j

The day of the year as a decimal number (001-366).

%k

The hour (24-hour clock) as a decimal number (0-23); single digits are preceded by a space.

%l

The hour (12-hour clock) as a decimal number (1-12); single digits are preceded by a space.

%M

The minute as a decimal number (00-59).

%m

The month as a decimal number (01-12).

%n

A newline character.

%P

Either am or pm.

%p

Either AM or PM.

%R

The time in the format %H:%M.

%r

The 12-hour clock time using AM/PM notation.

%S

The second as a decimal number (00-60).

%s

The number of seconds since 1970-01-01 00:00:00. Always overrides all other format elements, independent of where %s appears in the string. If multiple %s elements appear, then the last one takes precedence.

%T

The time in the format %H:%M:%S.

%t

A tab character.

%U

The week number of the year (Sunday as the first day of the week) as a decimal number (00-53).

%u

The weekday (Monday as the first day of the week) as a decimal number (1-7).

%V

The week number of the year (Monday as the first day of the week) as a decimal number (01-53). If the week containing January 1 has four or more days in the new year, then it is week 1; otherwise it is week 53 of the previous year, and the next week is week 1.

%W

The week number of the year (Monday as the first day of the week) as a decimal number (00-53).

%w

The weekday (Sunday as the first day of the week) as a decimal number (0-6).

%X

The time representation in HH:MM:SS format.

%x

The date representation in MM/DD/YY format.

%Y

The year with century as a decimal number.

%y

The year without century as a decimal number (00-99), with an optional leading zero. Can be mixed with %C. If %C is not specified, years 00-68 are 2000s, while years 69-99 are 1900s.

%%

A single % character.

%E#S

Seconds with # digits of fractional precision.

%E*S

Seconds with full fractional precision (a literal '*').

%E4Y

Four-character years (0001 ... 9999). Note that %Y produces as many characters as it takes to fully render the year.

Parse date

Parses a date from a string - learn more >

  • Return type: date

  • Fields:

    • format_string (format string)

      • required: true

      • input type: literal

      • allowed type(s): string

    • date_string (date string)

      • required: true

      • input type: variable or literal

      • allowed type(s): string

Category: dateTime

Current dateTime

Returns the current dateTime - learn more >

  • Return type: dateTime

  • Fields:

    • time_zone (UTC (default))

      • required: false

      • input type: enum

      • allowed type(s): any

New dateTime

Constructs a dateTime from a year, month, day, hour, minute, and second - learn more >

  • Return type: dateTime

  • Fields:

    • day (integer)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer

    • month (integer)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer

    • year (integer)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer

    • hour (integer)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer

    • minute (integer)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer

    • second (integer)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer

DateTime add

Add a period of time to a dateTime - learn more >

  • Return type: dateTime

  • Fields:

    • dateTime_expression (dateTime)

      • required: true

      • input type: variable or literal

      • allowed type(s): dateTime

    • integer_expression (number)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer

    • date_part (Date part (e.g., days))

      • required: true

      • input type: enum

      • allowed type(s): any

DateTime diff

Calculate distance between two dateTimes - learn more >

  • Return type: integer

  • Fields:

    • dateTime_expression (dateTime)

      • required: true

      • input type: variable or literal

      • allowed type(s): dateTime

    • dateTime_expression2 (number)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer

    • date_part (Date part (e.g., days))

      • required: true

      • input type: enum

      • allowed type(s): any

DateTime subtract

Subtract a period of time from a dateTime - learn more >

  • Return type: dateTime

  • Fields:

    • dateTime_expression (dateTime)

      • required: true

      • input type: variable or literal

      • allowed type(s): dateTime

    • integer_expression (number)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer

    • date_part (Date part (e.g., days))

      • required: true

      • input type: enum

      • allowed type(s): any

DateTime truncate

Truncates a dateTime to the nearest boundary - learn more >

  • Return type: dateTime

  • Fields:

    • dateTime_expression (dateTime)

      • required: true

      • input type: variable or literal

      • allowed type(s): dateTime

    • date_truncate_part (DateTime part (e.g., minutes))

      • required: true

      • input type: enum

      • allowed type(s): any

Format dateTime

Returns a formatted string from a dateTime - learn more >

  • Return type: string

  • Fields:

    • format_string (format string)

      • required: true

      • input type: literal

      • allowed type(s): string

    • dateTime_expression (dateTime)

      • required: true

      • input type: variable or literal

      • allowed type(s): dateTime

See more information about format strings.

Parse dateTime

Parses a dateTime from a string - learn more >

  • Return type: dateTime

  • Fields:

    • format_string (format string)

      • required: true

      • input type: literal

      • allowed type(s): string

    • dateTime_string (dateTime string)

      • required: true

      • input type: variable or literal

      • allowed type(s): string

Category: time

Current time

Returns the current time - learn more >

  • Return type: time

  • Fields:

Format time

Returns a formatted string from a time - learn more >

  • Return type: string

  • Fields:

    • format_string (format string)

      • required: true

      • input type: literal

      • allowed type(s): string

    • time_expression (time)

      • required: true

      • input type: variable or literal

      • allowed type(s): time

See more information about format strings.

Parse time

Parses a time from a string - learn more >

  • Return type: time

  • Fields:

    • format_string (format string)

      • required: true

      • input type: literal

      • allowed type(s): string

    • time_string (time string)

      • required: true

      • input type: variable or literal

      • allowed type(s): string

New time

Constructs a time from an hour, minute, and second - learn more >

  • Return type: time

  • Fields:

    • hour (integer)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer

    • minute (integer)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer

    • second (integer)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer

Time add

Add a period of time to a time - learn more >

  • Return type: time

  • Fields:

    • time_expression (time)

      • required: true

      • input type: variable or literal

      • allowed type(s): time

    • integer_expression (number)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer

    • date_part (Time part (e.g., minutes))

      • required: true

      • input type: enum

      • allowed type(s): any

Time diff

Calculate distance between two times - learn more >

  • Return type: integer

  • Fields:

    • time_expression (time)

      • required: true

      • input type: variable or literal

      • allowed type(s): time

    • time_expression2 (number)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer

    • date_part (Time part (e.g., minutes))

      • required: true

      • input type: enum

      • allowed type(s): any

Time subtract

Subtract a period of time from a time - learn more >

  • Return type: time

  • Fields:

    • time_expression (time)

      • required: true

      • input type: variable or literal

      • allowed type(s): time

    • integer_expression (number)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer

    • date_part (Time part (e.g., minutes))

      • required: true

      • input type: enum

      • allowed type(s): any

Time truncate

Truncates a time to the nearest boundary - learn more >

  • Return type: time

  • Fields:

    • time_expression (time)

      • required: true

      • input type: variable or literal

      • allowed type(s): time

    • date_truncate_part (Time part (e.g., minutes))

      • required: true

      • input type: enum

      • allowed type(s): any

Category: math

Absolute value

Returns the absolute value of a variable - learn more >

  • Return type: dynamic

  • Fields:

    • expression (variable)

      • required: true

      • input type: variable

      • allowed type(s): integer | float

Arithmetic

Compute simple arithmetic (+, -, *, /) - learn more >

  • Return type: dynamic

  • Fields:

    • expression (Value)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer | float

    • operator (operator)

      • required: true

      • input type: enum

      • allowed type(s): any

    • expression2 (Value)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer | float

Ceiling

Returns the smallest integral value that is not less than the provided value - learn more >

  • Return type: float

  • Fields:

    • expression (variable)

      • required: true

      • input type: variable

      • allowed type(s): float

Integer divide

Divide two integer values, rounding down any remainder - learn more >

  • Return type: integer

  • Fields:

    • expression (value)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer

    • expression2 (value)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer

e ^ x

Compute the natural exponential of a variable - learn more >

  • Return type: float

  • Fields:

    • expression (variable)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer | float

Floor

Returns the largest integral value that is not greater than the provided value - learn more >

  • Return type: float

  • Fields:

    • expression (variable)

      • required: true

      • input type: variable

      • allowed type(s): float

Greatest

Find the largest value of several variables - learn more >

  • Return type: dynamic

  • Fields:

    • expression (values)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer | float | date | dateTime | time | string | boolean

Is infinity

Return true if the value is positive or negative infinity, false otherwise. Returns NULL for NULL inputs - learn more >

  • Return type: boolean

  • Fields:

    • expression (variable)

      • required: true

      • input type: variable

      • allowed type(s): integer | float

Is NaN

Determines whether input value is not a number (NaN) - learn more >

  • Return type: boolean

  • Fields:

    • expression (variable)

      • required: true

      • input type: variable

      • allowed type(s): integer | float

Least

Find the smallest value of several variables - learn more >

  • Return type: dynamic

  • Fields:

    • expression (values)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer | float | date | dateTime | time | string | boolean

Log

Compute the logarithm of a variable to a provided base; generates an error if the variable is <= 0. If no base is provided, defaults to natural log - learn more >

  • Return type: float

  • Fields:

    • expression (variable)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer | float

    • literal (optional, defaults to natural log)

      • required: false

      • input type: literal

      • allowed type(s): integer | float

Mod

Modulo: compute the remainder of the division of two integers - learn more >

  • Return type: integer

  • Fields:

    • expression (dividend)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer

    • expression2 (divisor)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer

Power

Raises a value to a power - learn more >

  • Return type: float

  • Fields:

    • expression (variable or value)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer | float

    • expression2 (variable or value)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer | float

Random

Generate a pseudo-random float between [0, 1) - learn more >

  • Return type: float

  • Fields:

Round

Rounds a variable to the nearest integer (or, if specified, to the provided number of decimal places) - learn more >

  • Return type: float

  • Fields:

    • expression (variable)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer | float

    • literal (optional, defaults to 0)

      • required: false

      • input type: literal

      • allowed type(s): integer

Safe divide

Divide two values - learn more >

  • Return type: dynamic

  • Fields:

    • expression (Dividend)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer | float

    • expression2 (Divisor)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer | float

Sign

Returns the sign (-1, 0, +1) of a numeric variable - learn more >

  • Return type: integer

  • Fields:

    • expression (variable)

      • required: true

      • input type: variable

      • allowed type(s): integer | float

Sqrt

Compute the square root of a variable; generates an error if the variable is less than 0 - learn more >

  • Return type: float

  • Fields:

    • expression (variable)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer | float

Truncate

Similar to round, but rounds to the nearest integer whose absolute value is not greater than the absolute value of the provided variable (always rounds towards zero) - learn more >

  • Return type: float

  • Fields:

    • expression (variable)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer | float

    • literal (optional, defaults to 0)

      • required: false

      • input type: literal

      • allowed type(s): integer

Category: string

Concat

Concatenates multiple strings into one - learn more >

  • Return type: string

  • Fields:

    • expression (values)

      • required: true

      • input type: variable or literal

      • allowed type(s): string

Ends with

Determines whether a string is a suffix of another string - learn more >

  • Return type: boolean

  • Fields:

    • expression (string)

      • required: true

      • input type: variable or literal

      • allowed type(s): string

    • expression2 (suffix)

      • required: true

      • input type: variable or literal

      • allowed type(s): string

Format string

Creates a formatted string from input variables. Similar to the C printf function - learn more >

  • Return type: string

  • Fields:

    • format_string (format string)

      • required: true

      • input type: literal

      • allowed type(s): string

    • expression (value(s))

      • required: true

      • input type: variable or literal

      • allowed type(s): string | integer | float | date | dateTime | time

Length

Returns the number of characters in a string variable - learn more >

  • Return type: integer

  • Fields:

    • expression (variable)

      • required: true

      • input type: variable

      • allowed type(s): string

Lower case

Return string value to lowercase - learn more >

  • Return type: string

  • Fields:

    • expression (variable)

      • required: true

      • input type: variable

      • allowed type(s): string

Pad left

Pad a string to the left with characters up to a certain length - learn more >

  • Return type: string

  • Fields:

    • expression (string variable)

      • required: true

      • input type: variable

      • allowed type(s): string

    • return_length (length (integer))

      • required: true

      • input type: variable or literal

      • allowed type(s): integer

    • pattern (" "(space) - default)

      • required: false

      • input type: variable or literal

      • allowed type(s): string

Trim left

Removes all leading characters that match the provided pattern - learn more >

  • Return type: string

  • Fields:

    • expression (value)

      • required: true

      • input type: variable or literal

      • allowed type(s): string

    • pattern ((whitespace) - default)

      • required: false

      • input type: variable or literal

      • allowed type(s): any

Regexp contains

Match a value against a regular expression - learn more >

  • Return type: boolean

  • Fields:

    • expression (variable)

      • required: true

      • input type: variable

      • allowed type(s): string

    • regex (regular expression)

      • required: true

      • input type: literal

      • allowed type(s): any

Regexp extract

Returns the first substring that matches a regular expression - learn more >

  • Return type: string

  • Fields:

    • expression (variable)

      • required: true

      • input type: variable

      • allowed type(s): string

    • regex (regular expression)

      • required: true

      • input type: literal

      • allowed type(s): any

Regexp replace

Replaces all substrings that match a given regular expression with a new string - learn more >

  • Return type: string

  • Fields:

    • expression (string)

      • required: true

      • input type: variable or literal

      • allowed type(s): string

    • regex (regular expression)

      • required: true

      • input type: literal

      • allowed type(s): any

    • expression2 (replacement string)

      • required: true

      • input type: variable or literal

      • allowed type(s): string

Repeat

Return a string of a provided value repeated a set number of times - learn more >

  • Return type: string

  • Fields:

    • expression (Value or variable)

      • required: true

      • input type: variable or literal

      • allowed type(s): string

    • literal (# of repetitions)

      • required: true

      • input type: literal

      • allowed type(s): integer

Replace

Replaces all substrings that match a given string with a new string - learn more >

  • Return type: string

  • Fields:

    • expression (string)

      • required: true

      • input type: variable or literal

      • allowed type(s): string

    • match (match string)

      • required: true

      • input type: variable or literal

      • allowed type(s): string

    • expression2 (replacement string)

      • required: true

      • input type: variable or literal

      • allowed type(s): string

Reverse

Reverses a string - learn more >

  • Return type: string

  • Fields:

    • expression (string)

      • required: true

      • input type: variable or literal

      • allowed type(s): string

Pad right

Pad a string to the right with characters up to a certain length - learn more >

  • Return type: string

  • Fields:

    • expression (string variable)

      • required: true

      • input type: variable

      • allowed type(s): string

    • return_length (length (integer))

      • required: true

      • input type: variable or literal

      • allowed type(s): integer

    • pattern (" "(space) - default)

      • required: false

      • input type: variable or literal

      • allowed type(s): string

Trim right

Removes all trailing characters that match the provided pattern - learn more >

  • Return type: string

  • Fields:

    • expression (value)

      • required: true

      • input type: variable or literal

      • allowed type(s): string

    • pattern ((whitespace) - default)

      • required: false

      • input type: variable or literal

      • allowed type(s): any

Starts with

Determines whether a string is a prefix of another string - learn more >

  • Return type: boolean

  • Fields:

    • expression (string)

      • required: true

      • input type: variable or literal

      • allowed type(s): string

    • expression2 (prefix)

      • required: true

      • input type: variable or literal

      • allowed type(s): string

String position

Returns the 1-based index of the first occurrence of a substring within a string. Returns 0 if not found. - learn more >

  • Return type: integer

  • Fields:

    • expression (string)

      • required: true

      • input type: variable or literal

      • allowed type(s): string

    • match (match string)

      • required: true

      • input type: variable or literal

      • allowed type(s): string

Substring

Return the substring of a value - learn more >

  • Return type: string

  • Fields:

    • expression (value)

      • required: true

      • input type: variable or literal

      • allowed type(s): string

    • position (integer)

      • required: true

      • input type: variable or literal

      • allowed type(s): integer

    • length (integer)

      • required: false

      • input type: variable or literal

      • allowed type(s): integer

Trim

Removes all leading and trailing characters that match the provided pattern - learn more >

  • Return type: string

  • Fields:

    • expression (value)

      • required: true

      • input type: variable or literal

      • allowed type(s): string

    • pattern ((whitespace) - default)

      • required: false

      • input type: variable or literal

      • allowed type(s): any

Upper case

Return string value to uppercase - learn more >

  • Return type: string

  • Fields:

    • expression (variable)

      • required: true

      • input type: variable

      • allowed type(s): string