Articles on: Workflows

Function References Documentation

How to use functions within the formula node in workflows.


abs( number )

Calculate the absolute value of a numeric value.


Arguments:

  • number: a numeric scalar or column


add( x, y )

Adds two numeric values together.


Arguments:

  • x: a numeric scalar or column
  • y: a numeric scalar or column


and( value, ... )

Logical and operaion


Arguments:

  • value: boolean scalars or columns


between( value, lower, upper )

Check if value falls between the lower/upper bounds.


Arguments:

  • value: a scalar or column
  • lower: a scalar or column representing the lower bound
  • upper: a scalar or column representing the upper bound


convert( value, type )

Change the type of a value to a different data type.


Arguments:
*value: a scalar or column
*type: the type to convert to, accepts text, float, int, str, time, date and timestamp


ceiling( number )

Round a numeric value up to the nearest integer value greater than or equal to this value.


Arguments:
*number: a numeric scalar or column


coalesce( value, ... )

Returns the first non-null value from the passed arguments in left-to-right order


Arguments:
*value: scalars or columns, uses the first not null record


contains( text, pattern )

Determine if text exactly contains the given pattern.


Arguments:


  • text: a text scalar or column that should contain pattern
  • pattern: a text scalar or column that should be in text


date( year, month, day )

Create a date from year, month and day


Arguments:

  • year: a integer scalar or column providing the year
  • month: a integer scalar or column providing the month
  • day: a integer scalar or column providing the day


time( hour, minute, second )

Create a time from hour, minute and second


Arguments:

  • hour: a integer scalar or column providing the hour
  • minute: a integer scalar or column providing the minute
  • second: a integer scalar or column providing the second


extract_date( date )

Get date part from a timestamp value


day( date )

Get the day of month from a date or timestamp value


Arguments:
*date: a timestamp scalar or column


day_of_week( date )

Get day of the week from date or timestamp, starting from 1 Sunday to 7 Saturday


Arguments:

  • date: a timestamp scalar or column


divide( dividend, divisor )

Divide dividend by divisor


Arguments:

  • dividend: a numeric scalar or column
  • divisor: a numeric scalar or column


epoch_seconds( timestamp )

Seconds passed since 00:00:00 UTC on 1 January 1970 also called UNIX time


Arguments:
*timestamp: a timestamp scalar or column


exp( value )

Calculate exponential value


Arguments:
*value: a scalar or column


find( text, searchtext )

Returns position (0 indexed) of first occurence of searchtext in text


Arguments:

  • text: a text scalar or column
  • searchtext: a text scalar or column


floor( number )

Rounding down a numeric value to the greatest integer less or eqqual


Arguments:

  • number: a numeric scalar or column


fillnull( value, fill_value )

Replace nulls in value with fill_value


Arguments:

  • value: a scalar or column
  • fill_value: a scalar or column


hash( value )

Produces a cryptographic fingerprint from value using the Fingerprint64 method


Arguments:

  • value: a scalar or column


hour( time )

Get the hour from date or timestamp


Arguments:

  • time: a time scalar or column


ifelse( condition, true_value, false_value )

Returns the first value if condition is true if not it returns the second value


Arguments:

  • condition: a condition of a boolean column, scalar or another formula
  • true_value: scalar or column that is return if condition is true
  • false_value: scalar or column that is return if condition is false


isnull( value )

Check whether value is null


Arguments:

  • value: a scalar or column


join( delimiter, text, ... )

Concatenate text with the delimiter


Arguments:
*delimiter: a value that is used to separate the following arguments

  • text: a text scalar or column


json_extract( json, path ) - coming soon 🚀

Extract a field inside JSON, returns text


Arguments:

  • json: a text value in JSON format
  • path: path to the desired item in the format e.g. $.a.b[0]


left( text, nchars )

Return up to nchars characters starting from start of each text


Arguments:

  • text: a text scalar or column
  • nchars: a numeric scalar or column indicating the number of characters from left that are returned


length( text )

Calculate character length from text


Arguments:

  • text: a text scalar or column


like( text, pattern )

Compare text with another pattern, returns True if pattern is like text


Arguments:

  • text: a text scalar or column
  • pattern: a text scalar or column


ln( number )

Returns the natural logarithm of number


Arguments:

  • number: a numeric scalar or column


log( number, base )

Returns the logarithm with base of number


Arguments:

  • number: a numeric scalar or column
  • base: a numeric scalar or column


log2( number )

Returns the logarithm with base 2 of number


Arguments:

  • number: a number scalar or column


log10( number )

Returns the logarithm with base 10 of number


Arguments:

  • number: a numeric scalar or column


lower( text )

Turns text to lowercase


Arguments:

  • text: a text scalar or column


lpad( text, length [, fillchar] )

Returns string of given length by truncating (on left) or padding (on left) original string


Arguments:

  • text: a text scalar or column
  • length: a numeric scalar or column indicating the length of the resulting text
  • fillchar: a text scalar or column


ltrim( text )

Remove white space on the left of text


Arguments:

  • text: a text scalar or column


millisecond( time )

Get the milliseconds from date or timestamp


Arguments:

  • time: a time scalar or column


minute( time )

Get the minute from date or timestamp


Arguments:

  • time: a time scalar or column


month( date )

Get the month from date or timestamp


Arguments:

  • date: a timestamp scalar or column


modulo( x, y )

Compute the modulo of x and y


Arguments:

  • x: an integer scalar or column
  • y: an integer scalar or column


product( x, y )

Multiply x and y


Arguments:

  • x: a numeric scalar or column
  • y: a numeric scalar or column


notnull( value )

Check whether value is null


Arguments:

  • value: a scalar or column


now( )

Compute the current datetime


or( value, ... )

Logical or operaion


Arguments:

  • value: boolean scalars or columns


power( number, exponent )

Calculate the number to the power of exponent


Arguments:

  • number: a numeric scalar or column
  • exponent: a numeric scalar or column


regex_extract( text, pattern, index )

Returns specified index, 0 indexed, from text based on regex pattern given
👉 Read our in depth guide on regular expressions


Arguments:

  • text: a text scalar or column
  • pattern: a text scalar or column
  • index: a numeric scalar or column


regex_replace( text, pattern, replace )

Replaces match found in text by regex pattern with replace text.
👉 Read our in depth guide on regular expressions


Arguments:

  • text: a text scalar or column
  • pattern: a text scalar or column that is being replaced by
  • replace: a text scalar or column that replaces the pattern in text


regex_search( text, pattern )

Search regex pattern in text
👉 Read our in depth guide on regular expressions


Arguments:

  • text: a text scalar or column
  • pattern: a text scalar or column


repeat( text, n )

Repeat text n times


Arguments:

  • text: a text scalar or column
  • n: a numeric scalar or column


replace( text, pattern, replace )

Replaces each exact occurrence of pattern in text with replace


Arguments:

  • text: a text scalar or column
  • pattern: a text scalar or column
  • replace: a text scalar or column that replaces the pattern in text


reverse( text )

Reverse text character order


Arguments:

  • text: a text scalar or column


right( text, nchars )

Return up to nchar characters starting from end of each text.


Arguments:

  • text: a text scalar or column
  • nchars: a numeric scalar or column indicating the number of characters from right that are returned


round( number, digits )

Round number to digit decimal positon


Arguments:

  • number: a numeric scalar or column
  • digits: an integer scalar or column


rpad( text, length [, fillchar] )

Returns string of given length by truncating (on right) or padding (on right) original string


Arguments:

  • text: a text scalar or column
  • length: a numeric scalar or column
  • fillchar: a numeric scalar or column


rtrim( text )

Remove white space on the right of text


Arguments:

  • text: a text scalar or column


second( time )

Get the seconds from time or timestamp


Arguments:

  • time: a time scalar or column


sqrt( number )

Calculate the square root


Arguments:

  • number: a numeric scalar or column


parse_date( value, format )

Creates a date from a provided text and format
👉 Read our in depth guide on parsing


Arguments:

  • value: a text scalar or column in the representing a date in format
  • format: a text scalar or column defining the date format of value e.g '%Y-%m-%d'


parse_time( value, format )

Creates a time from a provided text and format
👉 Read our in depth guide on parsing


Arguments:

  • value: a text scalar or column in the representing a time in format
  • format: a text scalar or column defining the time format of value e.g '%Y-%m-%d'


parse_datetime( value, format )

Creates a datetime from a provided text and format
👉 Read our in depth guide on parsing


Arguments:

  • value: a text scalar or column in the representing a datetime in format
  • format: a text scalar or column defining the datetime format of value e.g '%Y-%m-%d'


format_datetime( datetime, format )

Format timestamp into string with given format
👉 Read our in depth guide on formatting


Arguments:

  • datetime: a datetime scalar or column
  • format: determines how the resulting text is formatted


trim( text )

Remove white space surrounding the text


Arguments:

  • text: a text scalar or column


to_json_string( dictionary )

Transform a dictionary to a JSON string


Arguments:

  • dictionary: a dictionary column or scalar


to_timezone( datetime, timezone )

Convert a date & time to a new date & time in a different timezone


Arguments:

  • datetime: a date & time scalar or column
  • timezone: the time zone for the new column in tz format e.g. 'US/Pacific' (see a list here)


subtract( minuend, subtrahend )

Substract subtrahend from minuend


Arguments:

  • minuend: a numeric scalar or column
  • subtrahend: a numeric scalar or column


subtract_days( date, days )

Substract days from date


Arguments:

  • date: a date scalar or column
  • days: an integer scalar or column of days to subtract


substitute( text, pattern [, replace] [, else] )

Replace text if with replace if pattern is equal to text optional replace with else


Arguments:

  • text: a text scalar or column
  • pattern: a text scalar or column
  • replace: a text scalar or column
  • else: a text scalar or column


extract_time( datetime )

Get the time from a timestamp


Arguments:

  • datetime: a datetime scalar or column


datetime_seconds( seconds, unit )

Create a datetime from an integer as the number of unit since from January 1st, 1970 UTC (unix time)


Arguments:

  • seconds: an integer scalar or column
  • unit: "s" for seconds, "ms" for milliseconds, "us" for microseconds


datetime_diff( minuend, subtrahend, unit )

Get the difference between minuend and subtrahend in the given unit


Arguments:

  • minuend: a datetime scalar or column
  • subtrahend: a datetime scalar or column
  • unit: unit of the difference e.g. "s" for seconds


today( )

Compute today's date


truncate( datetime, unit )

Zero out smaller-size units beyond indicated unit


Arguments:

  • datetime: a datetime scalar or column
  • unit: unit e.g. "s" for seconds


upper( text )

Capitalize text.


Arguments:

  • text: a text scalar or column


weekday( date )

Gets weekday from a date


Arguments:

  • date: a timestamp scalar or column


year( date )

Get the year from date


Arguments:

  • date: a timestamp scalar or column

Updated on: 05/10/2024

Was this article helpful?

Share your feedback

Cancel

Thank you!