Functions available to add formula columns

Functions available to add formula columns



Abs

abs(num)

Returns the absolute value of the numeric value.

Parameters
Name
Description

num

Decimal

Parameter must be a numeric value from a column or an expression that returns a number.
Example
Function
Result
abs(-1)
1


Div

div(numerator,denominator)

Performs floating-point division. Returns a decimal value.

Parameters
Name
Description

numerator

Decimal

Parameter must be a number, a numeric column or an expression that returns a number.

denominator

Decimal

Parameter must be a number, a numeric column or an expression that returns a number.
Example
Function
Result
div(3,2)
1.5


Add

add(num1,num2)

Returns param1+param2. Performs numeric addition.

Parameters
Name
Description

num1

Decimal

Parameter must be a numeric value or a Number column.

num2

Decimal

Parameter must be a numeric value or a Number column.
Example
Function
Result
add('3', '2')
5



Sub

sub(param1,param2)

Performs numeric subtraction. Returns param1 - param2.

Parameters
Name
Description

param1

Decimal

Parameter must be a numeric value.

param2

Decimal

Parameter must be a numeric value.
Example
Function
Result
sub('3', '2')
1



Mul

mul(param1,param2)

Performs numeric multiplication.

Parameters
Name
Description

param1

Decimal

Parameter must be a numeric value.

param2

Decimal

Parameter must be a numeric value.
Example
Function
Result
mul('3', '2')
6



Avg

avg(list)

Returns the average value in a numeric column or list

Parameters
Name
Description

list

Decimal

Parameter must be a numeric column, or a list of numbers
Example
ticket_count
30
40
50
60
70
80
90
Function
Result
avg(ticket_count);
60



Min

min(num)

Returns the minimum value in a list of numeric values

Parameters
Name
Description

num

Decimal

Parameter must be a numeric column or a list of numeric values
Example
ticket_count
30
40
50
60
70
80
90
Function
Result
min('ticket_count');
30



Max

max(num)

Returns the maximum value from a numeric column

Parameters
Name
Description

num

Decimal

Parameter must be a numeric column
Example
ticket_count
30
40
50
60
70
80
90
Function
Result
max('ticket_count');
90



Max Date

max_date(date)

Returns the maximum date value from a date or a date-time column.

Parameters
Name
Description

date

Datetime

Parameter must be a date column or a date-time column.
Example
Data column
06 Dec 2018
17 Nov 2019
20 Oct 2020
Function
Result
max_date(Data column)
20 Oct 2020



Min Date

min_date(date)

Returns the minimum date value from a date or a date-time column.

Parameters
Name
Description

date

Datetime

Parameter must be a date column or a date-time column.
Example
Data column
06 Dec 2018
17 Nov 2019
20 Oct 2020
Function
Result
min_date(Data column)
06 Dec 2018



Sum

sum(num)

Returns the sum of the values in the given column

Parameters
Name
Description

num

Decimal

Parameter must be a numeric column, or a list of numbers
Example
marks
45
33
56
70
Function
Result
sum(marks)
205



Mode

mode(num)

Returns the most occurring value in the column

Parameters
Name
Description

num

Decimal

Parameter must be a numeric column
Example
ticket_count
30
40
30
60
30
80
90
Function
Result
mode('ticket_count');
30



Count

count(col)

Returns the total number of values in the column. Null values (empty cells) are ignored.

Parameters
Name
Description

col

Text

Parameter must be a column
Example
ticket_count
30
40
50
60
70
80
90
Function
Result
count(ticket_count);
7



Count with Blanks

count_wb(col)

Returns the total number of values in the column including null values (empty cells).

Parameters
Name
Description

col

Text

Parameter must be a column
Example
ticket_count
30
40
50

70
80
90
Function
Result
count_wb(ticket_count);
7



Variance

variance(param)

Returns the variance calculated from values of a group in a number format.

Parameters
Name
Description

param

Decimal

Parameter must be a numeric group of values such as a Number column.
Example
ticket_count
30
40
50
60
70
80
90
Function
Result
variance("ticket_count")
466.66



Standard Deviation

stddev(param)

Returns the standard deviation calculated from the input values.

Parameters
Name
Description

param

Decimal

Parameter must be a numeric value or a Number column.
Example
Score
10
20
30
40
50
60
Function
Result
stddev(Score)
18.70



Binary

bin(param)

Returns the binary representation of a number.

Parameters
Name
Description

param

Number

Parameter must be a numeric value or a numeric column or an expression that returns a number.
Example
Function
Result
bin('111')
1101111



Cube Root

cbrt(param)

Returns the cube root of the number.

Parameters
Name
Description

param

Decimal

Parameter must be a number, or a numeric column or an expression that results in a numeric value.
Example
Function
Result
cbrt('27')
3



Ceiling

ceil(param)

Returns the smallest integer value that is bigger than or equal to a number.

Parameters
Name
Description

param

Decimal

Parameter must be a number, or a numeric column or an expression that results a numeric value.
Example
Function
Result
ceil('25.75')
26



Character

char(param,...)

Returns the ASCII character having the binary equivalent to the parameter. If n is larger than 256 the result is equivalent to char(n % 256).

Parameters
Name
Description

param

Multiple

Parameter must be a number, a numeric column, or an expression that returns a number.
Example
Function
Result
char('65')
A



Concat

concat(params)

Returns the concatenated text of the given parameters. Returns null, if any one of the parameters is null.

Parameters
Name
Description

params

Text

Accepts any number of parameters. Parameters must be a Text or a Text column, or an expression that returns a Text.
Example
Function
Result
concat("NOW", "HERE")
NOWHERE



Concat with Separator

concat_ws(params)

Returns the concatenated text of the given parameters along with the separator. Returns null, if any one of the parameter is null.

Parameters
Name
Description

params

Text

The first of the params is considered as the separator, using which the rest of the parameters will be concatenated. All parameters must be Text, Text columns, or expressions that returns a Text.
Example
Function
Result
concat_ws('_', 'NOW', 'HERE')
NOW_HERE



Convert

conv(param,from_base,to_base)

This function converts the number from 'from base' to 'to base'.

Parameters
Name
Description

param

Text

Parameter must be a number or a number column, or an expression that returns a number.

from_base

Decimal

The base value to be converted from.

to_base

Decimal

The base value to be converted to.
Example
Function
Result
conv(100, 2, 10)
4



Current Date

current_date()

Returns the current date as a date value. Function accepts no parameters.

Example
Function
Result
current_date()
09/21/2019



Current Timestamp

current_timestamp()

Returns the current timestamp as a date-time value. Function accepts no parameters.

Example
Function
Result
current_timestamp()
09/19/2019 19:18:55



Date Format

date_format(date,format)

Converts the date-timestamp to the format specified in the parameter.

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column to change the format.

format

Text

Specifies the format in which the date should be returned by the function.
Example
Data column
2017-06-15
Function
Result
date_format(Data column, "dd MMM, yyyy")
15 June, 2017
Note: If you wish to provide the date input directly into the function as shown below, please use either of these supported formats: “dd MMM, yyyy” or “dd MMM, yyyy HH:mm:ss"

Example function:
date_format(14 June 2017 18:20:56, dd MMM, yyyy HH:mm:ss) 

Function output: 
2017-06-14 18:20:56



Day of Month

day_of_month(date)

Returns the day of month of the date.

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column.
Example
Data column
2009-03-24
Function
Result
day_of_month(Data column)
24


Day of Week
day_of_week(date)

Returns the day of the week for the given date. [1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Saturday]

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column.
Example
Data column
06-12-2018
Function
Result
day_of_week(Data column)
5



Week Day

weekday(date)

Returns the day of the week from the given date

Parameters
Name
Description

date

Datetime

Parameter must be a date column or a date-time column
Example
Data column
06 Dec 2018
Function
Result
weekday(Data column);
3



Day of the Week in name

day_of_week_name(date)

Returns the day of the week (as name) for the given date.

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column.
Example
Data column
06 Dec 2018
Function
Result
day_of_week_name(Data column)
Thursday



Day of the Year

day_of_year(date)

Returns the day of year (in number) of the date/timestamp.

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column.
Example
Data column
25-03-2009
Function
Result
day_of_year(Data column)
84



Decode

decode(param,charset)

Returns the decoded value of the first argument using the second argument character set (UTF-8, ASCII, UTF-10, etc.).

Parameters
Name
Description

param

Text

Parameter must be a Text, a Text column, or an expression that returns a Text.

charset

Text

Specifies the character set such as UTF-8, ASCII, UTF-10, etc.
Example
Function
Result
decode("ABC", "UTF-8")
ABC



Degrees

degrees(param)

Converts radians to degrees.

Parameters
Name
Description

param

Decimal

Specifies the angle in radians.
Example
Function
Result
degrees(3.141592653589793)
180.0



Encode

encode(param,charset)

Returns the encoded value of the first argument using the second argument character set (utf-8, ascii, utf-10, etc.).

Parameters
Name
Description

param

Text

Parameter must be a Text, a Text column, or an expression that returns a Text.

charset

Text

Specifies the character set. 'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16' are the supported character sets.
Example
Function
Result
encode("ABC", "UTF-8")
ABC



Exponential

exp(param)

Returns the exponential value of the given number. This value is the exponent (e) raised to the power specified as the parameter.

Parameters
Name
Description

param

Decimal

Parameter must be a number or an expression that returns a number.
Example
Function
Result
exp(1)
2.718281828


Floor

floor(param)

Returns the largest integer that is less than or equal to the given parameter.

Parameters
Name
Description

param

Decimal

Parameter must be a number or an expression that returns a number.
Example
Function
Result
floor(-0.1)
-1



Greatest

greatest(params)

Returns the largest value from a group of values.

Parameters
Name
Description

params

Decimal

Supports multiple parameters, The parameters must be a number column, or a list of numbers or an expression that returns a list of numbers.
Example
Function
Result
greatest(10, 9, 2, 4, 3)
10



Hexadecimal

hex(param)

Returns the hexadecimal value of the given parameter.

Parameters
Name
Description

param

Text

Parameter can be any value or a column of Number, Text or Boolean type.
Example
Function
Result
hex(17)
11



Hour

hour(datetime)

Returns the hour component of the timestamp.

Parameters
Name
Description

datetime

Datetime

Specifies a date-time column.
Example
Data column
2009-03-07 20 18 34 543
Function
Result
hour(Data column)
20



If

if(condition,param1,param2)

If the condition is true, then returns param1; otherwise returns param2.

Parameters
Name
Description

condition

Boolean

An expression that returns a Boolean.

param1

Text

Parameter can be any value or a column of any type.

param2

Text

Parameter can be any value or a column of any type.
Example
Function
Result
if(1 < 2, 'a', 'b')
a



If Null

if_null(param1,param2)

Returns param2 if param1 is null, returns param1 otherwise.

Parameters
Name
Description

param1

Text

Parameter can be any value or a column of any type.

param2

Text

Parameter can be any value or a column of any type.
Example
Function
Result
if_null(null, 'b')
b



Initcap

initcap(param)

Returns the text with the first letter of each word in uppercase. All other letters are in lowercase. Words are delimited by white space.

Parameters
Name
Description

param

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.
Example
Function
Result
initcap('zoho one is an operating system for your business')
Zoho One Is An Operating System For Your Business



Is Null

is_null(param)

Returns true if the expression or column value is null or empty

Parameters
Name
Description

param

Text

Parameter can be a column, or an expression
Example
Function
Result
is_null(null)
true
is_null('A')
false



Is not Null

is_not_null(param)

Returns true if the column value or expression is not null or empty

Parameters
Name
Description

param

Text

Parameter can be a column, or an expression
Example
Function
Result
is_not_null(null)
false
is_not_null('A')
true



Not

not(param)

Applies the NOT logical operation on the column. Returns the opposite value of the Boolean value specified.

Parameters
Name
Description

param

Boolean

Specifies a Boolean value on which the not logical operation is to be applied on.
Example
Function
Result
not(5 > 3)
false



Last Day

last_day(datetime)

Returns the last day of the month mentioned in the given date.

Parameters
Name
Description

datetime

Datetime

Specifies a date column or a datetime column.
Example
Data column
2016-04-09
Function
Result
last_day(Data column)
2016-04-30



Least

least(params)

Returns the least value of all, skipping the null values.

Parameters
Name
Description

params

Decimal

The parameters must be a number column, or a list of numbers or an expression that returns a list of numbers.
Example
Age
[20, 21, 24, 45, 46, 54, 60, 61, 63]
Weight
[123, 145, 160, 155, 200, 50, 134, 55, 60]
Function
Result
least('Age', 'Weight')
20, 21, 24, 45, 46, 50, 60, 55, 60



Left

left(param,length)

Returns the leftmost 'length' of characters from the first parameter, if the length is less than or equal to 0 the result is an empty text.

Parameters
Name
Description

param

Text

Parameter must be a Text, a Text column, or an expression that returns a Text.

length

Number

length must be a number or a column of Number type.
Example
Function
Result
left("NOWHERE", 3)
NOW



Length

length(param)

Returns the character length of the text parameter.

Parameters
Name
Description

param

Text

Parameter must be a Text, a Text column, or an expression that returns a Text.
Example
Function
Result
length("NOWHERE")
7


In

ln(param)

Returns the natural logarithm (base e) of the given parameter.

Parameters
Name
Description

param

Decimal

Parameter must be a number, a numeric column or an expression that returns a number.
Example
Function
Result
ln('1')
0



Log

log(base,param)

Returns the logarithm of parameter given. This function also accepts custom base value for the logarithm.

Parameters
Name
Description

base

Decimal

Indicates the base of the logarithm function. Parameter must be a number, a numeric column or an expression that returns a number.

param

Decimal

Parameter must be a number, a numeric column or an expression that returns a number.
Example
Function
Result
log(10,100)
2



Log10

log10(param)

Returns the logarithm (base 10) of the parameter given.

Parameters
Name
Description

param

Decimal

Parameter must be a number, a numeric column or an expression that returns a number.
Example
Function
Result
log10('10.0')
1




Log2

log2(param)

Returns the logarithm (base 2) of parameter given.

Parameters
Name
Description

param

Decimal

Parameter must be a number, a numeric column or an expression that returns a number.
Example
Function
Result
log2('2.0')
1



Lower

lower(param)

The LOWER function returns the given text with lower case for each character.

Parameters
Name
Description

param

Text

Specifies the Text that needs to be converted with lower case for each character. The parameter can be a text value, a text column or an expression that results in a text value.
Example
Function
Result
lower("Zoho, Your Life's Work, Powered By Our Life's Work!")
zoho, your life's work, powered by our life's work!



Itrim

ltrim(param)

Removes the leading white space characters from the text.

Parameters
Name
Description

param

Text

Parameter must be a Text, a Text column, or an expression that returns a Text.
Example
Function
Result
ltrim(" Zoho")
Zoho



Minute

minute(datetime)

Returns the minute component of the timestamp.

Parameters
Name
Description

datetime

Datetime

Specifies a date-time column.
Example
Data column
2009-07-30 12 58 59
Function
Result
minute(Data column)
58


Minute of the day

minute_of_day(datetime)

Returns the minute component of the day from the timestamp.

Parameters
Name
Description

datetime

Datetime

Specifies a date-time column.
Example
Data column
2009-07-30 12 58 59
Function
Result
minute_of_day(Data column)
778



Minute of the Hour

minute_of_hour(datetime)

Returns the minute component of an hour from the timestamp.

Parameters
Name
Description

datetime

Datetime

Specifies a date-time column.
Example
Data column
2009-07-30 12 58 59
Function
Result
minute_of_hour(Data column)
58



Millisecond of the Day

millisecond_of_day(datetime)

Returns the millisecond of a day from the timestamp.

Parameters
Name
Description

datetime

Datetime

Specifies a date-time column.
Example
Data column
2009-07-30 00 03 00
Function
Result
millisecond_of_day(Data column)
180000


Millisecond of Second

millisecond_of_second(datetime)

Returns the millisecond of a second from the timestamp.

Parameters
Name
Description

datetime

Datetime

Specifies a date-time column.
Example
Data column
2009-07-30 12 58 59 850
Function
Result
millisecond_of_second(Data column)
850



Is Leap Year

is_leap_year(date)

Returns true for a leap year and false otherwise.

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column .
Example
Data column
2016-04-09
Function
Result
is_leap_year(Date column)
true



Mod

mod(param1,param2)

Returns the remainder after performing numeric division on two numbers.

Parameters
Name
Description

param1

Decimal

Parameter must be a numeric value which is the numerator used in the numeric division.

param2

Decimal

Parameter must be a numeric value which is the denominator used in the numeric division.
Example
Function
Result
mod('7','5')
2



Month

month(date)

Extracts month from the date/timestamp.

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column.
Example
Data column
Nov 04
2016
Function
Result
month(Data column)
11


Month as Name

month_name(date)

Returns the month's name of the date/timestamp.

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column.
Example
Data column
1997-02-28
Function
Result
month_name(Data column)
February



Month and Year

month_year(date)

Returns the month name of the date/timestamp along with the year.

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column.
Example
Data column
1997-02-28
Function
Result
month_year(Data column)
February, 1997



Months in-between

months_between(date1,date2)

Returns the number of months between the two specified dates.

Parameters
Name
Description

date1

Datetime

Specifies a date column or a date-time column from which the difference in months is to be determined.

date2

Datetime

Specifies a date column or a date-time column from which the difference in months is to be determined.
Example
Data column 1
1997-02-28
Data column 2
1997-10-28
Function
Result
months_between('Data column 1', 'Data column 2')
8


Negative

negative(param)

Returns the negated value of the number mentioned in param.

Parameters
Name
Description

param

Decimal

Specifies a number value that is to be negated.
Example
Function
Result
negative('1')
-1



Pi

pi()

Returns the Pi value. The number π is a mathematical constant. It is defined as the ratio of a circle's circumference to its diameter.

Example
Function
Result
pi()
3.1415926536



Power

pow(param1,param2)

Raises a number to the power that is specified by a number.

Parameters
Name
Description

param1

Decimal

Specifies the number which is to be raised to a certain power.

param2

Decimal

Specifies the number which represents the power that the number is to be raised to.
Example
Function
Result
pow('2', '3')
8



Quarter

quarter(date)

Extract quarter of the year, in the range 1 to 4 from the date.

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column.
Example
Data column
2016-08-31
Function
Result
quarter(Data column)
3



Quarter Name

quarter_name(date)

Extract quarter of the year, in the range 1 to 4, with the name of the year from date.

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column.
Example
Data column
2016-08-31
Function
Result
quarter_name(Data column)
Q3



Quarter Year

quarter_year(date)

Extract quarter of the year, in the range 1 to 4, with the year from the date.

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column.
Example
Data column
2016-08-31
Function
Result
quarter_year(Data column)
Q3, 2016



Radians

radians(param)

Converts angle in degrees to radian on the column.

Parameters
Name
Description

param

Decimal

angle in degrees
Example
Function
Result
radians(180)
3.1415926536



Random

random(num1,num2)

Generates a random integer value, between the given numbers, in the column.

Parameters
Name
Description

num1

Decimal

Specifies the starting number for the range from which a random number is returned.

num2

Decimal

Specifies the ending number for the range from which a random number is returned.
Example
Function
Result
random('0', '4')
3



Regex Extract

regexp_extract(str,regexp,groupIdx)

Extracts a group that matches regex expression.

Parameters
Name
Description

str

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

regexp

Text

Parameter must be a regular expression pattern passed as a Text.

groupIdx

Number

[Optional] Paramter must be an integer which indicates the index of the group that needs to be extracted.
Example
Function
Result
regexp_extract("100abc", "\\d+" , 0)
100
regexp_extract( "100-abc" , "(\\d+)-([a-zA-Z]+)" , 2)
abc



Regex Replace

regexp_replace(str,regex,replace_with)

Replaces all the subtexts of a text 'str' that matches the regex with the text mentioned in 'replace_with'.

Parameters
Name
Description

str

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

regex

Text

Parameter must be a Text specifying the regular expression.

replace_with

Text

Parameter must be a Text specifying the regular expression.
Example
Function
Result
regexp_replace("100-200", "(\\d+)", 'num')
num-num



Replace

replace(str,search,replace)

Replaces all occurrences of search with the provided replacement text.

Parameters
Name
Description

str

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

search

Text

A Text expression. If search is not found in str, str is returned unchanged.

replace

Text

A Text expression. If replace is not specified or is an empty text, nothing replaces the text that is removed from str.
Example
Function
Result
replace('ABCabc', 'abc', 'DEF')
ABCDEF



Reverse

reverse(str)

Returns the reversed version of the given text.

Parameters
Name
Description

str

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.
Example
Function
Result
reverse('Zoho One')
enO ohoZ



right(str,len)

Returns the rightmost length `len` of characters from the text `str`. If the length is less than or equal to 0 the result is an empty text.

Parameters
Name
Description

str

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

len

Number

Specifies the number of characters from the right of the Text to be returned.
Example
Function
Result
right('Zoho One', '3')
One



Round Number

round_num(param,d)

Returns a decimal number rounded to a specified number of decimal places using HALF_EVEN rounding mode.

Parameters
Name
Description

param

Decimal

Specifies the number column or the exact decimal number to be rounded off to the nearest value.

d

Number

Specifies the number column or the exact number of decimal places that the number should be rounded off to.
Example
Function
Result
round_num('2.5', '0')
2
round_num('5.5', '0')
6
round_num('3.2', '0')
3


rtrim

rtrim(str)

Removes the trailing space characters from the provided text value.

Parameters
Name
Description

str

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.
Example
Function
Result
rtrim('Zoho One ')
Zoho One


Second

second(datetime)

Returns the seconds component of the date time. The function also supports unix timestamp in milliseconds as the input.

Parameters
Name
Description

datetime

Datetime

Specifies a date-time column.
Example
Data column
2009-07-30 12 58 59
Function
Result
second(Data column)
59



Second of the Day

second_of_day(datetime)

Returns the second component of a day from the timestamp. The function also supports unix timestamp in milliseconds as the input.

Parameters
Name
Description

datetime

Datetime

Specifies a date-time column.
Example
Data column
2009-07-30 00 11 00
Function
Result
second_of_day(Data column)
660


SHA1

Syntaxsha(param)

Returns a sha1 hash value as a hex text of the param.

Parameters
Name
Description

param

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.
Example
Function
Result
sha('Zoho One')
a74457c58f9d3e564bf534d96e1d85d6e513dac4



Soundex

soundex(str)

Returns the Soundex code of the text values in the column.

Parameters
Name
Description

str

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.
Example
Function
Result
soundex('Miller')
M460


Square Root
sqrt(param)

Returns the square root of the values on the column.

Parameters
Name
Description

param

Decimal

Parameter must be a number or an expression that results in a numeric value or a Number column.
Example
Function
Result
sqrt('64')
8



Substring

substr(sentence,pos,len)

Returns the subtext of the sentence text that starts at a position index and is of a given length.

Parameters
Name
Description

sentence

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

pos

Number

Parameter must be a number representing the index of the starting position of the text to be extracted.

len

Number

[Optional] Parameter must be a number representing the length of the text to be extracted from the 'sentence'
Example
Function
Result
substr('Zoho One', '4', '1')
o



Trim

trim(param)

Removes the leading and trailing space characters from the text values in the column.

Parameters
Name
Description

param

Text

Parameter can be a column of any datatype.
Example
Function
Result
trim(' Zoho One ')
Zoho One



Upper

upper(param)

Returns a text with all of the characters changed to uppercase.

Parameters
Name
Description

param

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.
Example
Function
Result
upper('Zoho One')
ZOHO ONE



Week of the Year

week_of_year(date,startDay,week_mode)

Returns the week of year (in number) from the date or timestamp (a week is considered to start on a Monday and week 1 is the first week with more than 3 days).

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column.

startDay

Number

[Optional] Specifies the starting day of the week. The value range from 1 to 7 in this order. [1 = Sunday, 2 = Monday, 3 = Tuesday, 4 = Wednesday, 5 = Thursday, 6 = Friday, 7 = Saturday]. The default value is 1.

week_mode

Number

[Optional] Week mode determines how the function works. Values allowed are '1' (ISO Week Mode - Minimal 4 days), '2' (week starts from January 1st of the calendar year). The default value is 1.
Example
Data column
06/12/2018
Function
Result
week_of_year(Data column, 1, 1)
49



Week of the Year

week_of_month(date)

Returns the week of the month of the given date.

Parameters
Name
Description

date

Datetime

Specifies the date or a date-time.
Example
Data column
06/13/2018
Function
Result
week_of_month(Data column)
Week 2


Week of the Year with Year

week_of_year_with_year(datetime,startDay,week_mode)

Returns the week of the year with year from the given date

Parameters
Name
Description

datetime

Datetime

Parameter must be a datetime type column

startDay

Number

[Optional] Specifies the starting day of the week. The value range from 1 to 7 in this order. [1 = Sunday, 2 = Monday, 3 = Tuesday, 4 = Wednesday, 5 = Thursday, 6 = Friday, 7 = Saturday]. The default value is 1.

week_mode

Number

[Optional] Week mode determines how the function works. Values allowed are '1' (ISO Week Mode - Minimal 4 days), '2' (week starts from January 1st of the calendar year). The default value is 1.
Example
Data column
06 Dec 2018
17 Nov 2019
Function
Result
week_of_year_with_year(Data column, 1, 1)
Week 49 2018, Week 46 2019



Year

year(date)

Returns the year from the given date

Parameters
Name
Description

date

Datetime

Parameter must be a date or a datetime column
Example
Data column
06 Dec 2018
17 Nov 2019
Function
Result
year(Data column)
2018, 2019


Century

century(date)

Returns the century of the given date or the date-time input.

Parameters
Name
Description

date

Datetime

Parameter must be of date type or a date-time type.
Example
Data column
06 Dec 2018
17 Nov 2019
Function
Result
century(Data column)
21



Extract Date

extract_date(datetime)

Returns the date part from the given date column, or a date-time column.

Parameters
Name
Description

datetime

Datetime

Parameter must be of datetime type or an expression that returns a datetime.
Example
Data column
2009-03-17 20 18 34 543
Function
Result
extract_date(Data column)
2009-03-17



Extract Time

extract_time(datetime)

Returns the time part from the given date-time column.

Parameters
Name
Description

datetime

Datetime

Parameter must be of datetime type or an expression that returns a datetime.
Example
Data column
2009-05-07 12 03 34
Function
Result
extract_time(Data column)
12:03:34



Add Years

add_years(datetime,num_of_years)

Adds the specified number of years to the given date-time value.

Parameters
Name
Description

datetime

Datetime

Specifies the date-time column to increment.

num_of_years

Decimal

Specifies the number of years by which to increment the timestamp value.
Example
Data column
03/25/2009
Function
Result
add_years(Data column, '3')
03/25/2012



Sub years

sub_years(datetime,num_of_years)

Subtracts the specified number of years from the given date-time value.

Parameters
Name
Description

datetime

Datetime

Specifies the date-time column or the exact date to decrement.

num_of_years

Decimal

Specifies the number of years by which to decrement the timestamp value.
Example
Data column
06/12/2018
Function
Result
sub_years(Data column, '3')
06/12/2015



Difference in Years

diff_in_years(startDate,endDate)

Returns the number of years between startDate and endDate.

Parameters
Name
Description

startDate

Datetime

Specifies a date column or a date-time column, the exact start date or an expression that results in a date value.

endDate

Datetime

Specifies a date column or a date-time column, the exact end date or an expression that results in a date value.
Example
Data column 1
2013-08-10
Data column 2
2009-03-07
Function
Result
diff_in_years(Data column 1, Data column 2)
4



Add Weeks

add_weeks(datetime,num_of_weeks)

Adds the specified number of weeks to the given date-time value.

Parameters
Name
Description

datetime

Datetime

Specifies the date-time column to increment.

num_of_weeks

Decimal

Specifies the number of weeks by which to increment the timestamp value.
Example
Data column
03/25/2009
Function
Result
add_weeks(Data column, '2')
04/08/2009



Sub Weeks

sub_weeks(datetime,num_of_weeks)

Subtracts the specified number of weeks in the given date-time value.

Parameters
Name
Description

datetime

Datetime

Specifies the date-time column or the exact date to decrement.

num_of_weeks

Decimal

Specifies the number of weeks by which to decrement the timestamp value.
Example
Data column
06/12/2018
Function
Result
sub_weeks(Data column, '2')
11/22/2018



Difference in Weeks

diff_in_weeks(startDate,endDate)

Returns the number of weeks between startDate and endDate.

Parameters
Name
Description

startDate

Datetime

Specifies a date column or a date-time column, the exact start date or an expression that results in a date value.

endDate

Datetime

Specifies a date column or a date-time column, the exact end date or an expression that results in a date value.
Example
Data column 1
2009-08-10
Data column 2
2009-09-17
Function
Result
diff_in_weeks(Data column 1, Data column 2)
7



Add Months

add_months(date,num_of_months)

Adds the specified number of months to the specified date value

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column

num_of_months

Decimal

Specifies the number of months to add to the date value
Example
Data column
2009-11-25
Function
Result
add_months(Data column, `3`)
2010-02-25


Sub Months

sub_months(datetime,num_of_months)

Subtracts the specified number of months from the given date value.

Parameters
Name
Description

datetime

Datetime

Specifies a date column or a date-time column.

num_of_months

Decimal

Specifies the number of months by which to decrement the date.
Example
Data column
19/12/2016
Function
Result
sub_months(Data column, '3')
19/09/2016



Difference in Months

diff_in_months(startDate,endDate)

Returns the number of months between startDate and endDate.

Parameters
Name
Description

startDate

Datetime

Specifies a date column or a date-time column.

endDate

Datetime

Specifies a date column or a date-time column.
Example
Data column 1
2009-08-17
Data column 2
2009-03-10
Function
Result
diff_in_months(Data column 1, Data column 2)
5



Add Days

add_days(date,num_of_days)

Adds the specified number of days to the specified date value.

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column to increment.

num_of_days

Decimal

Specifies the number of days by which to increment the dates.
Example
Data column
12/25/2009
Function
Result
add_days(Data column, `30`)
01/24/2010



Sub Days

sub_days(date,num_of_days)

Subtracts the specified number of days from the specified date value.

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column to decrement.

num_of_days

Decimal

Specifies the number of days by which to decrement the date.
Example
Data column
2009-12-25
Function
Result
sub_days(Data column, '5')
2009-12-20


Difference in Days

diff_in_days(startDate,endDate)

Returns the number of days between startDate and endDate.

Parameters
Name
Description

startDate

Datetime

Specifies a date column or a date-time column.

endDate

Datetime

Specifies a date column or a date-time column.
Example
Data column 1
2009-03-10
Data column 2
2009-03-07
Function
Result
diff_in_days(Data column 1, Data column 2)
3



Add Hours

add_hours(datetime,num_of_hours)

Adds the specified number of hours to the given date column or a date-time column.

Parameters
Name
Description

datetime

Datetime

Specifies the date-time column to increment.

num_of_hours

Decimal

Specifies the number of hours by which the value will be incremented.
Example
Data column
12/08/2018 07 58 59
Function
Result
add_hours(Data column, '3')
12/08/2018 10:58:59



Sub Hours

sub_hours(datetime,num_of_hours)

Subtracts the specified number of hours from the given date value.

Parameters
Name
Description

datetime

Datetime

Specifies the date-time column to decrement.

num_of_hours

Decimal

Specifies the number of hours by which to decrement the timestamp value.
Example
Data column
1965-07-27 11 58 59
Function
Result
sub_hours(Data column, 3)
1965-07-27 8:58:59



Difference in Hours

diff_in_hours(startDateTimestamp,endDateTimestamp)

Returns the number of hours between startDate timestamp and endDate timestamp.

Parameters
Name
Description

startDateTimestamp

Datetime

Specifies a date-time column.

endDateTimestamp

Datetime

Specifies a date-time column.
Example
Data column 1
2009-03-07 23 20 40 600
Data column 2
2009-03-07 20 18 34 543
Function
Result
diff_in_hours(Data column 1, Data column 2)
3



Add Minutes

add_minutes(datetime,num_of_minutes)

Adds the specified number of minutes to the given date-time value.

Parameters
Name
Description

datetime

Datetime

Specifies the date-time column to increment.

num_of_minutes

Decimal

Specifies the number of minutes by which to increment the timestamp value.
Example
Data column
12/08/2018 11 58 59
Function
Result
add_minutes(Data column, '2')
12/08/2018 12:00:59



Sub Minutes

sub_minutes(datetime,num_of_minutes)

Subtracts the specified number of minutes from the given date-time value.

Parameters
Name
Description

datetime

Datetime

Specifies the date-time column to decrement.

num_of_minutes

Decimal

Specifies the number of minutes by which to decrement the timestamp value.
Example
Data column
2018-07-18 00 10 00
Function
Result
sub_minutes(Data column, 2)
2018-07-18 00:08:00



Difference in Minutes

diff_in_minutes(startDateTimestamp,endDateTimestamp)

Returns the number of minutes between startDate timestamp and endDate timestamp.

Parameters
Name
Description

startDateTimestamp

Datetime

Specifies a date-time column.

endDateTimestamp

Datetime

Specifies a date-time column.
Example
Data column 1
2009-03-07 20 20 34 600
Data column 2
2009-03-07 20 18 34 543
Function
Result
diff_in_minutes(Data column1, Data column 2)
2



Add Seconds

add_seconds(datetime,num_of_seconds)

Adds the specified number of seconds to the given date-time value.

Parameters
Name
Description

datetime

Datetime

Specifies the date-time column to increment.

num_of_seconds

Decimal

Specifies the number of seconds by which to increment the timestamp value.
Example
Data column
03/25/2009 11 58 49
Function
Result
add_seconds(Data column, '10')
03/25/2009 11:58:59



Sub Seconds

sub_seconds(datetime,num_of_seconds)

Subtracts the specified number of seconds in the given date-time value.

Parameters
Name
Description

datetime

Datetime

Specifies the date-time column to decrement.

num_of_seconds

Decimal

Specifies the number of seconds by which to decrement the timestamp value.
Example
Data column
06/12/2018 00 03 00
Function
Result
sub_seconds(Data column, '10')
06/12/2018 00:02:50


Difference in Seconds

diff_in_seconds(startDateTimestamp,endDateTimestamp)

Returns the number of milliseconds between startDate timestamp and endDate timestamp.

Parameters
Name
Description

startDateTimestamp

Datetime

Specifies a date-time column.

endDateTimestamp

Datetime

Specifies a date-time column.
Example
Data column 1
2009-03-07 20 20 36 600
Data column 2
2009-03-07 20 20 34 543
Function
Result
diff_in_seconds(Data column 1, Data column 2)
02


Invert Case

invert_case(param)

Returns the text with all case inverted i.e. upper case is converted to lower case and vice versa.

Parameters
Name
Description

param

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.
Example
Function
Result
invert_case('Zoho One is an Operating System for your Business')
zOHO oNE IS AN oPERATING sYSTEM FOR YOUR bUSINESS

Alternate Case
alternate_case(string)

Returns a text with alternate lower and upper case for each character of the input text starting with lower case for the first character.

Parameters
Name
Description

string

Text

Specifies the Text that needs to be converted with alternate case for each character.
Example
Function
Result
alternate_case("Zoho, Your Life's Work, Powered By Our Life's Work!")
zOhO, yOuR LiFe's wOrK, pOwErEd bY OuR LiFe's wOrK!


Sentence Case

sentence_case(param)

Returns a text of the values in the columns after applying the sentence case to it.

Parameters
Name
Description

param

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.
Example
Function
Result
sentence_case('welcome to the united states of america')
"Welcome to the united states of america"


Camel Case

camel_case(string)

Returns the text after applying the camel case to it.

Parameters
Name
Description

string

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.
Example
Function
Result
camel_case("Zoho one is an operating system for your business")
ZohoOneIsAnOperatingSystemForYourBusiness


Title Case

title_case(param)

Returns a text of the values in the columns after applying the title case to it. Title case means that the first letter of each word is capitalized, except for certain small words, such as articles and short prepositions

Parameters
Name
Description

param

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.
Example
Function
Result
title_case('united states of america')
United States of America


Lines to Space

lines_to_space(param)

Returns a text value where the new lines are converted to spaces.

Parameters
Name
Description

param

Text

Parameter must be a Text, a Text column, or an expression that returns a Text.
Example
Function
Result
lines_to_space('Zoho\nOne')
Zoho One


Space to Lines

space_to_lines(param)

Returns a text value where the spaces are converted to new lines.

Parameters
Name
Description

param

Text

Parameter must be a Text, a Text column, or an expression that returns a Text.
Example
Function
Result
space_to_lines('Zoho One')
Zoho\nOne


Tab to Space

tab_to_space(param)

Returns a text value where the tabs are converted to spaces.

Parameters
Name
Description

param

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.
Example
Function
Result
tab_to_space('Zoho\tOne')
Zoho One


Space to Tab

space_to_tab(param)

Returns a text value where the spaces are converted to tabs.

Parameters
Name
Description

param

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.
Example
Function
Result
space_to_tab('Zoho One')
Zoho\tOne


To Single Space

to_single_space(param)

Returns a text with multiple spaces into a single space.

Parameters
Name
Description

param

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.
Example
Function
Result
to_single_space('Zoho   Office   Suite')
Zoho Office Suite


To Single Tab

to_single_tab(param)

Returns a text with multiple tab spaces into a sinlge tab space.

Parameters
Name
Description

param

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.
Example
Function
Result
to_single_tab('Zoho\t\t\tOffice\tSuiteSQL\tOrg')
Zoho\tOffice\tSuiteSQL\tOrg


Remove Empty Lines

remove_empty_lines(param)

Removes all the empty lines in the column

Parameters
Name
Description

param

Text

Parameter must be a Text, a Text column, or an expression that returns a Text.
Example
Function
Result
remove_empty_lines('Zoho\n\n\nOffice\n\nSuite')
Zoho\nOffice\nSuite


Truncate Character

truncate_char(param1,param2)

Truncates a given number of characters from the input text value.

Parameters
Name
Description

param1

Text

Parameter can be a column of any datatype.

param2

Number

Specifies the number of characters to be removed from the Text. The value of the parameter should be smaller than the length of the Text.
Example
Function
Result
truncate_char(Zoho One, 4)
Zoho


Truncate Words

truncate_words(param1,param2)

Truncates a given number of words from the input text value.

Parameters
Name
Description

param1

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

param2

Number

Specifies the number of words to be removed from the Text. The value of the parameter should be smaller than the number of words in the Text and greater than 0.
Example
Function
Result
truncate_words('Zoho One', 1)
Zoho


Remove Space

remove_space(str)

Removes all the spaces in the column.

Parameters
Name
Description

str

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.
Example
Function
Result
remove_space(' Zoho One ')
"ZohoOne"


To Unix Timestamp

to_unix_timestamp(date)

Returns the UNIX timestamp of the given date time values.

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column.
Example
Data column
06/12/2018 00 03 00
Function
Result
to_unix_timestamp(Data column)
1544034780


Is Missing

is_missing(param)

Returns true for missing values, false otherwise.

Parameters
Name
Description

param

Text

Parameter can be a column of any datatype.
Example
Sales
11
12
' '
67
Function
Result
is_missing(Sales)
[false, false, true, false]


Is Valid

is_valid(param1,param2)

Returns true for valid values; false otherwise.

Parameters
Name
Description

param1

Text

Parameter must be a column conforming to any one data type.

param2

Text

Data type to verify if param1 is valid. It can be one of these values: text, date, number, datetime, decimal, currency, length, temperature, boolean, email, URL, list, map and custom data types
Example
Column1
"ABC"
"DEF"
"GHI"
Function
Result
is_valid(`Column1`,'text')
TRUE


Is Invalid

is_invalid(param1,param2)

Returns true for invalid values; false otherwise.

Parameters
Name
Description

param1

Text

Parameter must be a column conforming to any one data type.

param2

Text

Data type to verify if param1 is invalid. It can be one of these values: text, date, number, datetime, decimal, currency, length, temperature, boolean, email, URL, list, map and custom data types
Example
Column1
"ABC"
"DEF"
"GHI"
Function
Result
is_invalid(`Column1`, 'Number')
TRUE


Size

size(param)

Returns the size of a list. Returns -1 if null.

Parameters
Name
Description

param

List

Parameter must be a column of type List, or a group of values as a list.
Example
Function
Result
size(['b', 'd', 'c', 'a'])
4


List Concat

list_concat(param1,param2)

Returns a list that contains all the elements in the first list followed by all the elements in the second. The two input lists must contain items in the same data structure format.

Parameters
Name
Description

param1

List

Specifies the first of the two lists to concatenate.

param2

List

Specifies the second of the two lists to concatenate.
Example
column1
[1,2,3,4,5]
column2
["A","B,"C","D","E" ]
Function
Result
list_concat(column1, column2)
["1","2","3","4","5","A","B","C","D","E"]


List Unique

list_unique(param)

Filters the list by removing duplicate entries from it. Returns the filtered list.

Parameters
Name
Description

param

List

Parameter must be of type list or a List column.
Example
list
[30, 40, 50, 30, 70, 30, 90]
Function
Result
list_unique(list)
["40", "50", "70", "90"]


List Intersect

list_intersect(param1,param2)

This function compares the values of two lists, and returns only the matching elements. The two lists must contain items in the same data structure format.

Parameters
Name
Description

param1

List

The list to compare from.

param2

List

A list to compare against.
Example
list1
[1,2,3,4,5]
list2
["A", 1, "B" ,2, "C", 3, "D", 4, "E"]
Function
Result
list_intersect(list1, list2)
["1", "2", "3", "4"]


List Zip

list_zip(param1,param2)

Converts two unidimensional list columns into a multi dimensional list.

Parameters
Name
Description

param1

List

Parameter must be of type list or a List column.

param2

List

Parameter must be of type list or a List column.
Example
list1
["A","B","C"]
list2
["D","E","F"]
Function
Result
list_zip(list1 , list2)
[["A","D"],["B","E"],["C","F"]]


Split

split(str,regex)

Splits the text around occurrences that match the regular expression.

Parameters
Name
Description

str

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

regex

Text

Parameter must be a Text mentioning the regular expression that returns a Text.
Example
Function
Result
split('oneAtwoBthreeC', '[ABC]')
["one","two","three",""]


Collect Set

collect_set(param)

Collects the distinct values from a column as a list.

Parameters
Name
Description

param

Text

Parameter must be a column conforming to any one data type.
Example
Column1
"Australia"
"United States"
"Australia"
"Canada"
"Canada"
Function
Result
collect_set(Column1)
["Australia","United States","Canada"]


Contains

contains(string1,string2,num)

Returns true if the text string1 contains string2

Parameters
Name
Description

string1

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

string2

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

num

Number

Parameter must be a numeric value. Using '0' will make the search case-sensitive in finding the Text. Using any other number will render the search case-insensitive.
Example
Function
Result
contains("Pineapple", "Apple", 0)
True


Starts With

starts_with(string1,string2,number)

Compares two text columns and returns true if the text string1 starts with string2.

Parameters
Name
Description

string1

Text

Specifies Text values or a Text column.

string2

Text

Specifies Text values or a Text column

number

Number

Specifies if the comparison should be case-sensitive (input '1') or non case-sensitive (input '0').
Example
Function
Result
starts_with('Sunday', 'Sun', '1')
True
starts_with('Sunday', 'Mon', '0')
False


Ends With

ends_with(string1,string2,num)

Returns true if the text string1 ends with string2.

Parameters
Name
Description

string1

Text

Parameter must be a Text, a Text column, or an expression that returns a Text.

string2

Text

Parameter must be a Text, a Text column, or an expression that returns a Text.

num

Number

Parameter must be a numeric value. Using '0' will make the search case-sensitive in finding the sting. Using any other number will render the search case-insensitive.
Example
Function
Result
ends_with("Newyork","york", 0)
True


Extract Date With Format

extract_date_with_format(datetime,format)

Returns the date component from date column in the given format.

Parameters
Name
Description

datetime

Date

Parameter must be a date column or a date-time column.

format

Text

Specifies the format in which the date should be extracted by the function.
Example
Data column
2009-05-17
Function
Result
extract_date_with_format(Data column, 'dd-MM-yyyy')
17-05-2009


Extract Time With Format

extract_time_with_format(datetime,format)

Returns the time component from datetime column in the given format.

Parameters
Name
Description

datetime

Datetime

Parameter is mandatory and it must be a date-time type column.

format

Text

Parameter is mandatory and it must be a Text representing the time format to extract.
Example
Data column
2009-05-07 12 03 34 433
Function
Result
extract_time_with_format(Data column, 'hh:mm:ss:SSS')
12:03:34:433


Keyword Extraction

keyword_extraction(string,maxKeywordLength,topElements,model)

Returns the keywords present in the column.

Parameters
Name
Description

string

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

maxKeywordLength

Number

Maximum number of words in a single keyword set. For example, for the sentence, 'Operating Systems' key length can be at most two.

topElements

Number

Pick top N elements from results.

model

Text

Three models are supported. They are namely, ONLY_VALID_KEYWORDS, RANKED_KEYWORDS, RANKED_KEYWORDS_WITH_INDEX.
Example
Function
Result
keyword_extraction(`A room without books is like a body without a soul.` , '1' , '4' , 'ONLY_VALID_KEYWORDS')
('room','books','body','soul')


Language Prediction

lang_predict(string,langType)

Detect language from the given text. Supports 71 languages.

Parameters
Name
Description

string

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

langType

Text

Parameter must be either be 'code', or 'name'. Code will display the language code as a result, whereas name will display the name of the language.
Example
Function
Result
lang_predict('மதிப்புரைகளைப் படிக்கவும்' , 'name')
Tamil
lang_predict('மதிப்புரைகளைப் படிக்கவும' , 'code')
ta


Min If

min_if(num,condition)

Returns the min value on satisfying the given condition

Parameters
Name
Description

num

Decimal

Parameter must be a Number column

condition

Boolean

Condition must return a Boolean value based on which the function is calculated
Example
ticket_count
30
40
50
60
70
80
90
Function
Result
min_if(ticket_count, ticket_count!=30)
40


Max If

max_if(num,condition)

Returns the max value on satisfying the given condition

Parameters
Name
Description

num

Decimal

Parameter must be a Number column

condition

Boolean

Condition must return a Boolean value based on which the function is calculated
Example
ticket_count
30
40
50
60
70
80
90
Function
Result
max_if(ticket_count, ticket_count!=90)
80


Avg If

avg_if(num,condition)

Returns the average value on satisfying the given condition

Parameters
Name
Description

num

Decimal

Parameter must be a Number column

condition

Boolean

Condition must return a Boolean value based on which the function is calculated
Example
ticket_count
30
40
50
60
70
80
90
Function
Result
avg_if(ticket_count, ticket_count!=30)
65


Sum If

sum_if(num,condition)

Returns the sum of the values on satisfying the given condition

Parameters
Name
Description

num

Decimal

Parameter is mandatory and must be a numeric column or value

condition

Boolean

Condition must return a Boolean value based on which the function is calculated
Example
marks
[90, 85, 95, 65]
grade
[A, B, A, D]
Function
Result
sum_if(marks, `grade`=='A')
[185, 185, 185, 185]


Mode If

mode_if(num,condition)

Returns the mode value on satisfying the given condition

Parameters
Name
Description

num

Decimal

Parameter must be a Number column

condition

Boolean

Condition must return a Boolean value based on which the function is calculated
Example
ticket_count
30
40
30
40
30
80
90
Function
Result
mode_if(ticket_count, ticket_count!=30)
40


Standard Deviation If

stddev_if(number,condition)

Returns the population standard deviation of the given column after applying the specified condition.

Parameters
Name
Description

number

Decimal

Parameter must be a numeric column's value or an expression that returns a number.

condition

Boolean

Condition must return a boolean value based on which the function is calculated.
Example
Country
India
USA
Germany
Movie
Avatar
Inception
Avatar
Rating
9.432
1.0
2.0
Function
Result
stddev_if(Rating, `Movie`=='Avatar')
5.2552175978


Variance If

variance_if(num,condition)

Returns the variance of the values on satisfying the given condition

Parameters
Name
Description

num

Decimal

Parameter is mandatory and must be a numeric column or an expression that returns a list of numbers

condition

Boolean

Condition must return a Boolean value based on which the function is calculated
Example
sales_amount
9.432
1
2
country
India
USA
India
Function
Result
variance_if(sales_amount, 'country'=='India');
27.6173124, 27.617312, 27.617312


Count If

count_if(col,condition)

Returns the total number of values on satisfying the given condition

Parameters
Name
Description

col

Text

Parameter must be a column

condition

Boolean

Condition must return a Boolean value based on which the function is calculated
Example
ticket_count
30
40
50
60
70
80
90
Function
Result
count_if(ticket_count, ticket_count!=30)
6


Count Distinct

count_distinct(col)

Returns the total number of distinct values in the column

Parameters
Name
Description

col

Text

Parameter must be a column
Example
ticket_count
30
40
30
60
70
60
90
Function
Result
count_distinct(ticket_count);
5


Count Distinct If

count_distinct_if(col,condition)

Returns the total number of distinct values satisfying the given condition

Parameters
Name
Description

col

Text

Parameter must be a column

condition

Boolean

Condition must return a Boolean value based on which the function is calculated
Example
ticket_count
30
40
50
60
30
80
40
Function
Result
count_distinct_if(ticket_count, ticket_count!=30)
4


Range

range(startNum,stopNum,stepNum)

Computes an array of integers beginning from 'startNum' to 'stopNum' having an interval of 'stepNum' between consecutive integers in the range. If the function generates more than 1000 values for a cell, the output is a null value and the 'stopNum' is excluded from the output. 'StepNum' must always be a positive integer. If 'startNum' is greater than 'stopNum', steps are automatically converted to negative values and the output will be a decrement range.

Parameters
Name
Description

startNum

Number

Parameter must be a Number column or a number. Decimal values are not supported.

stopNum

Number

Parameter must be a Number column or a number. Decimal values are not supported.

stepNum

Number

Specifies the step value for the range. Decimal values are not supported.
Example
Function
Result
range('2','14','2')
[2, 4, 6, 8, 10, 12]
range('14','2','2')
[14, 12, 10, 8, 6, 4]


Locate

locate(string1,string2,position)

Returns the position of the first occurrence of the text 'string1' in 'string2' after the 'position' given

Parameters
Name
Description

string1

Text

Parameter must be a Text, a Text column or an expression that returns a Text

string2

Text

Parameter must be a Text, a Text column or an expression that returns a Text

position

Number

[Optional] Specifies the position after which 'string2' should be located.
Example
Function
Result
locate('bar', 'foobarbar')
4
locate('bar', 'foobarbar' , 5)
5


Lpad

lpad(string,length,string2)

Returns the 'string' text left-padded with a 'padString' for the 'length' specified. Padding will be applied only when 'length' value is more than the length of the text, if not the output text is shortened to the 'length' parameter.

Parameters
Name
Description

string

Text

Parameter must be a Text, a Text column or an expression that returns a Text

length

Number

Specifies the total length of the output Text

string2

Text

Specifies the Text that must be added to, or replaced in the 'Text1' value
Example
Function
Result
lpad('abcd', 3, 'A')
Aab
lpad('abcd', 7, 'A')
AAAabcd


Rpad

rpad(string,length,string2)

Returns the 'string' text right-padded with a 'padString' for the 'length' specified. Padding will be applied only when 'length' value is more than the length of the text, if not the output text is shortened to the 'length' parameter.

Parameters
Name
Description

string

Text

Parameter must be a Text column or an expression that returns a Text

length

Number

Specifies the total length of the output Text

string2

Text

Specifies the Text that must be added to, or replaced in the 'Text1' value
Example
Function
Result
rpad('abcd', 5, 'A')
abcdA
rpad('abcd', 7, 'A')
abcdAAA


Next as List

nest_as_list(params)

Converts the given parameters to a list

Parameters
Name
Description

params

Text

All parameters must be a Text, or a Text column, or an expression that returns a Text
Example
Function
Result
nest_as_list('Arjun', 'Zip code: 308-242-0670', 'Status: New resident')
["Arjun", "Zip code: 99501", "Status: New resident"]


Nest as Map

nest_as_map(params,...)

Converts the given parameters to a map. The first value will be the key, and the second will be the value in making each key-value pair.

Parameters
Name
Description

params

Multiple

All parameters must be a column
Example
details
['Name', 'Arjun', 'Zip code', '308-242-0670']
Function
Result
nest_as_map(details)
{'Name' : 'Arjun', 'Zip code' : '308-242-0670'}


ELT

elt(num,string,...)

Returns the text value available at the index number specified in the first argument

Parameters
Name
Description

num

Number

Parameter must be a numerical value, a numerical column or an expression that returns a number

string

Multiple

All parameters must be a Text column or an expression that returns a Text
Example
Function
Result
elt('5', 'Zoho', 'is the', 'operating system', 'for your', 'business')
business


Field

field(string1,string2,...)

Searches the value in the first argument with all the rest of the arguments and returns the position of the argument where the first match is found

Parameters
Name
Description

string1

Text

First parameter must be a Text, a Text column or an expression that returns a Text

string2

Multiple

Each parameter from the second must be a Text, a Text column or an expression that returns a Text
Example
Function
Result
field('as','has','as','have')
2



Insert

insert(string1,position,length,string2)

Inserts the text string2 in string1 at the index 'position' for the specified 'length'

Parameters
Name
Description

string1

Text

Parameter must be a Text, a Text column or an expression that returns a Text

position

Number

The position where the 'Text2' parameter must be inserted

length

Number

The length of the Text

string2

Text

Specifies the Text that must be added to, or replaced in the 'Text1' value
Example
Function
Result
insert('abcddb', 3, 2, 'efgh')
abefghdb


Repeat

repeat(string,num)

Repeats the given 'string' text for the specified number of times and returns the constructed text.

Parameters
Name
Description

string

Text

Parameter must be a Text column or a Text, or an expression that returns a Text

num

Number

Parameter must be a numerical value, a numerical column or an expression that returns a number
Example
Function
Result
repeat('Abcd', 3)
AbcdAbcdAbcd


Percentile

percentile(num,percentage,frequency)

Returns the exact percentile value of 'num' at the given 'percentage'

Parameters
Name
Description

num

Decimal

Parameter must be a Number column

percentage

Text

Parameter must be a Number column or a number, or an expression that returns a value between 0.0 to 1.0

frequency

Number

[Optional] Parameter must be a Number column or a number, or an expression that returns a positive number
Example
ticket_count
30
40
50
60
70
80
90
Function
Result
percentile(ticket_count, 0.3, 0)
3.0


Acos

acos(num)

Returns the inverse cosine or arc cosine of the input expressed in radians

Parameters
Name
Description

num

Decimal

Parameter must be a numeric value, a numeric column or an expression that returns a number in the range of [-1...1]
Example
Function
Result
acos(0.34);
1.22387943


Asin

asin(num)

Returns the arc sine or inverse sine of the input expressed in radians

Parameters
Name
Description

num

Decimal

Parameter must be a number, or a numeric column, or an expression that returns a number in the range of [-1...1]
Example
Function
Result
asin(0.34);
19.876874


Atan

atan(num)

Returns the arc tangent or inverse tangent of the input expressed in radians

Parameters
Name
Description

num

Decimal

Parameter must be a number, or a numeric column, or an expression that returns a number in the range of [-1...1]
Example
Function
Result
atan(0.34);
18.77803322


Atan

atan2(num1,num2)

Returns arc tangent or inverse tangent for two numeric values

Parameters
Name
Description

num1

Decimal

The parameter must be a number, or numeric columns, or expressions that returns a number

num2

Decimal

The parameter must be a number, or numeric columns, or expressions that returns a number
Example
Function
Result
atan2(5, 5);
0.7853981633974483


Cos

cos(num)

Returns the trigonometric cosine of the given angle in radians.

Parameters
Name
Description

num

Decimal

Parameter must be a numeric value, a numeric column or an expression that returns a number
Example
Function
Result
cos(0);
1.0


Sin

sin(num)

Returns the trigonometric sine of the given angle in radians.

Parameters
Name
Description

num

Decimal

Parameter must be a number, or a numeric column, or an expression that returns a number
Example
Function
Result
sin(0)
0.0


Tan

tan(angle)

Returns the trigonometric tangent of a given angle in radians.

Parameters
Name
Description

angle

Decimal

Parameter must be a number, or a numeric column, or an expression that returns a number
Example
Function
Result
tan(9.432)
0.0072221648


Cot

cot(num)

Returns the trigonometric cotangent of a given angle in radians.

Parameters
Name
Description

num

Decimal

Parameter must be a numeric value, a numeric column, or an expression that returns a number
Example
Function
Result
cot(0.25)
3.916317


Sign

sign(number)

Detects whether the value in the numeric column is positive, negative or zero

Parameters
Name
Description

number

Decimal

Specifies the number column for which the function is to be applied
Example
Function
Result
sign('-24')
-1

Is Last NDayis_last_nday(date,num)

Returns true if the given date value is within the previous N days from today

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column

num

Number

Specifies the number of days
Example
Data column
06/15/2020
Function
Result
is_last_nday(Data column, 10)
false

Is Last NMonthis_last_nmonth(date,num)

Returns true if the given date value is within the previous N months from the current month

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column

num

Number

Specifies the number of months
Example
Data column
05/15/2020
Function
Result
is_last_nmonth(Data column, 3)
false


Is Last NQuarter

is_last_nquarter(date,num)

Returns true if the given date value is within the previous N quarters from the current quarter

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column

num

Number

Specifies the number of quarters
Example
Data column
05/15/2020
Function
Result
is_last_nquarter(Data column, 3)
false


Is Last NYear

is_last_nyear(date,num)

Returns true if the given date value is within the previous N years from the current year

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column

num

Number

Specifies the number of years
Example
Data column
06/15/2019
Function
Result
is_last_nyear(Data column, 10)
false


Is Current Month

is_current_month(date)

Returns true if the given date or the date-time value falls under the current month.

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column.
Example
Data column
06/15/2020
Function
Result
is_current_month(Data column)
false


Is Current Quarter

is_current_quarter(date)

Returns true if the given date or the date-time value falls under the current quarter.

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column.
Example
Data column
07/15/2000
Function
Result
is_current_quarter(Data column)
false


Is Current Week

is_current_week(date,startDay)

Returns true if the given date or the date-time value falls under the current week. The starting day of the week is Sunday by default. This value can be changed using the 'startDay' parameter.

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column.

startDay

Number

Specifies the starting day of the week. The value range from 0 to 6 in this order. [0 = Sunday, 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday]
Example
Data column
06/15/2020
Function
Result
is_current_week(Data column, 1)
false


Is Current Year

is_current_year(date)

Returns true if the given date or datetime value falls under the current year.

Parameters
Name
Description

date

Datetime

Specifies a date column or a datetime column.
Example
Data column
07/15/2000
Function
Result
is_current_year(Data column)
false


Is Next NDay

is_next_nday(date,num)

Returns true if the given date value falls in the next N days from today

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column

num

Number

Specifies the number of days
Example
Data column
06/18/2020
Function
Result
is_next_nday(Data column, 10)
false


Is Next NMonth

is_next_nmonth(date,num)

Returns true if the given date value falls under the next N months from this month

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column

num

Number

Specifies the number of months
Example
Data column
06/18/2020
Function
Result
is_next_nmonth(Data column, 3)
false


Is Next NQuarter

is_next_nquarter(date,num)

Returns true if the given date value falls under the next N quarters from this quarter

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column

num

Number

Specifies the number of quarters
Example
Data column
06/18/2020
Function
Result
is_next_nquarter(Data column, 1)
false


Is Next NYear

is_next_nyear(date,num)

Returns true if the given date value falls under the next N years from this year

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column

num

Number

Specifies the number of years
Example
Data column
06/15/2020
Function
Result
is_next_nyear(Data column, 2)
false


Start Date

start_date(date,unit)

Returns start date for the given date value using the given date units

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column.

unit

Text

Specifies the unit of the date column. The date units can be 'week', 'week_sunday', 'week_monday', 'month', 'quarter' and 'year'.
Example
Data column
28/10/2020
Function
Result
start_date(Data column, 'month')
01/10/2020


End Date

end_date(date,units)

Returns the end date for the given date value using the given date units.

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column over which the function is applied

units

Text

Specifies the unit of the date column. The date units can be 'week', 'week_sunday', 'week_monday', 'month', 'quarter' and 'year'.
Example
Data column
2018-08-27
Function
Result
end_date(Data column, 'month')
2018-08-31


ASCII

ascii(string)

Returns the ASCII value of the specified character

Parameters
Name
Description

string

Text

Parameters must be a Text or a Text column, or an expression that returns a Text
Example
Function
Result
ascii('A')
65


Bit Length

bit_length(string)

Returns the value of the length of the text parameter as bit length

Parameters
Name
Description

string

Text

Parameters must be a Text, or a Text column, or an expression that returns a Text
Example
Function
Result
bit_length("AA")
16


Index Of

index_of(string1,string2)

Returns the index of the first occurrence of the text 'string2' in 'string1'

Parameters
Name
Description

string1

Text

The parameter must be a Text, a Text column, or an expression that returns a Text

string2

Text

The parameter must be a Text, a Text column, or an expression that returns a Text
Example
Function
Result
index_of('abcddb','db')
5


Space

space(number)

Returns a text consisting of n spaces.

Parameters
Name
Description

number

Number

Specifies number of spaces required. Parameter can be a number column specifying the number of spaces.
Example
Function
Result
space('2')
' '


Unhex

unhex(HexVal)

Returns the corresponding character for each pair of hexadecimal digits

Parameters
Name
Description

HexVal

Text

Parameter must be a hexadecimal Text value
Example
Function
Result
unhex('476F64');
God


Tomorrow

tomorrow()

Returns the date after the current date

Example
Function
Result
tomorrow();
02/06/2020


Yesterday

yesterday()

Returns the date of the previous day from the current date

Example
Function
Result
yesterday();
28/05/2020


Year and Week

year_week(date)

Returns the year and week of the given date

Parameters
Name
Description

date

Datetime

Parameter must be a date column or a date-time column
Example
Data column
06 Dec 2018
17 Nov 2018
Function
Result
year_week(Data column);
201848, 201846


String Compare

string_compare(string1,string2)

Compares two text columns and returns 0 if the columns are of equal size, 1 if column 1 is bigger than column 2 and returns -1 if column 2 is bigger than column 1.

Parameters
Name
Description

string1

Text

Specifies Text values or a Text column

string2

Text

Specifies Text values or a Text column
Example
Function
Result
string_compare('Sunday', 'Monday')
0


Correlation

correl(numList1,numList2)

Returns the Pearson Coefficient of correlation between two numerical columns

Parameters
Name
Description

numList1

Decimal

parameter must be a number column

numList2

Decimal

parameter must be a number column
Example
Age
[20, 21, 24, 45, 46, 54, 60, 61, 63]
Weight
[123, 145, 160, 155, 200, 150, 134, 133, 120]
Function
Result
correl('Age', 'Weight');
-0.1316


First Date of the Current Week

first_date_current_week(date,startDay)

Returns the day the current week begins with

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column.

startDay

Number

Specifies the starting day of the week. The value range from 0 to 6 in this order. [0 = Sunday, 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday]
Example
Data column
11 Oct 2013
Function
Result
first_date_current_week(`Data column`, 4)
09 Oct 2013


Is Previous Week

is_previous_week(date,startDay)

Returns true if the given date value falls in the previous week based on the start day of the week

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column

startDay

Number

Specifies the starting day of the week. The value range from 0 to 6 in this order. [0 = Sunday, 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday]
Example
Data column
10 Nov 2100
Function
Result
is_previous_week(`Data column`, 0)
false


Is Next Week

is_next_week(date,startDay)

Returns true if the given date value falls in the next week based on the start day of the week

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column

startDay

Number

Specifies the starting day of the week. The value range from 0 to 6 in this order. [0 = Sunday, 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday]
Example
Data column
20 Nov 1900
Function
Result
is_next_week(`Data column`, 1)
false


Current Time

current_time()

Returns the current time. Function accepts no parameters.

Example
Function
Result
current_time()
19:18:55


Parse Date

parse_date(date,outputFormat,inputFormats,...)

Returns date in the required format if the given value falls under any of the given formats

Parameters
Name
Description

date

Text

Specifies a column with dates in different formats

outputFormat

Text

Specifies the date format that the given date should be parsed into

inputFormats

Multiple

Specifies the possible date formats as a list present in the date column
Example
dateCol
Mon 23 Mar 2020
17/02/2020
03/21/2020
04/24/2020 16_25
Function
Result
parse_date(dateCol, 'EEE dd MMM,yyyy', 'dd/MM/yyyy', 'MM/dd/yyyy', 'MM/dd/yyyy', MM/dd/yyyy HH_mm)
Mon 23 Mar,2020, Mon 17 Feb,2020, Sat 21 Mar,2020


Convert Timezone

convert_timezone(datetime,current_tz,convert_tz)

Returns the date and time value in converted timezone

Parameters
Name
Description

datetime

Datetime

Specifies the date-time column.

current_tz

Text

The current time zone value should be in 'hh:mm' format

convert_tz

Text

The difference in time between the current time zone and the alternate zone
Example
Data column
2004-01-01 12 00 00
Function
Result
convert_timezone(Data column,'+00:00','+10:00')
2004-01-01 22:00:00


Seconds To Time

sec_to_time(seconds)

Converts the given seconds into time and returns it in the format HH:mm:ss. If given number is negative, a minus sign will be added before the result.

Parameters
Name
Description

seconds

Number

Specifies the date time column or the exact number of seconds to be converted. The parameter can also be unix timestamp in milliseconds.
Example
Function
Result
sec_to_time('86399')
23:59:59


Remove Symbols

remove_symbols(string)

Removes all characters from a text that are not letters, numbers, accented Latin characters, non-Latin characters, or whitespace.

Parameters
Name
Description

string

Text

Parameter must be a Text column or a Text, or an expression that returns a Text.
Example
Function
Result
remove_symbols('Abcd$%```')
Abcd


Substring in-between

substring_between(fulltext,start_text,end_text,pos)

Returns the characters between two given texts from the main text.

Parameters
Name
Description

fulltext

Text

All parameters must be of Text or a Text column, or an expression that returns a Text

start_text

Text

Specifies the Text value from which the sub text should be obtained

end_text

Text

Specifies the Text value to which the sub text should be obtained

pos

Number

[Optional] It is an optional positive numeric value which specifies the position of the matching delimiter from which the sub text should be obtained
Example
Function
Result
substring_between('Sunday', 'n', 'y', 1)
da


Substring Index

substring_count(fulltext,sub_text)

Returns the count of occurrence of given sub text in the given text

Parameters
Name
Description

fulltext

Text

The parameter must be of Text or a Text column, or an expression that returns a Text

sub_text

Text

Specifies the sub text whose count of occurrences is to be obtained
Example
Function
Result
substring_count('Sunday', 'day')
1


Substring Index

substring_index(fulltext,delimiter,count)

Returns the sub text before the delimiter, based on the count of occurrences of the delimiter in the given text column

Parameters
Name
Description

fulltext

Text

All parameters must be of Text or a Text column, or an expression that returns a Text

delimiter

Text

Specifies a delimiter based on which the sub text is to be obtained from the given value

count

Number

It is a numeric value which specifies the number of delimiters before which the sub text should be obtained
Example
Function
Result
substring_index('how.are.you', '.' , 1)
how


Microsecond

microsecond(datetime)

Returns the microsecond value from the given date-time value

Parameters
Name
Description

datetime

Datetime

Specifies a date time column
Example
Data column
1990-08-07 10 35 23 3427
Function
Result
microsecond(Data column)
342700


Make Date

make_date(year,numOfDays)

Returns the date value by adding the number of days to the given year

Parameters
Name
Description

year

Number

Specifies the year which must be a number, a numeric column or an expression that returns a number

numOfDays

Number

Specifies the number of days which must be a number, a numeric column or an expression that returns a number
Example
Function
Result
make_date('2020', '30')
01/30/2020


Add Time

add_time(datetime,time)

Adds the specified time to the given date-time column.

Parameters
Name
Description

datetime

Datetime

Specifies a date-time column.

time

Text

Specifies the time to increase the date-time.
Example
Data column
12/08/2018 07 58 59
Function
Result
add_time(Data column, '01:00:00')
12/08/2018 08:58:59


Sub Time

sub_time(datetime,time)

Returns the datetime by subtracting the specified time from the given value with time value

Parameters
Name
Description

datetime

Datetime

Specifies a date-time column

time

Text

Specifies the time to decrement from the date-time value. Value can be in the format `HH:mm:ss`
Example
Data column
12/08/2018 07 58 59
Function
Result
sub_time(Data column, '01:00:00')
12/08/2018 06:58:59


Period Add

period_add(yearMonth,numOfMonths)

Adds the specified number of months to the first parameter and returns in the 'year-month' format

Parameters
Name
Description

yearMonth

Number

Parameter must be a Number column or a number, or an expression that returns a number

numOfMonths

Number

Parameter must be a Number column or a number, or an expression that returns a number
Example
Function
Result
period_add(20208,6)
20212


Period Difference

period_diff(yearMonth1,yearMonth2)

Returns the difference between two periods in months

Parameters
Name
Description

yearMonth1

Number

Parameter must be a Number column or a number, or an expression that returns a number

yearMonth2

Number

Parameter must be a Number column or a number, or an expression that returns a number
Example
Function
Result
period_diff('198608','198602')
6


To Currency

to_currency(col,currency)

Converts a numeric column to the currency format and datatype (value conversion between currencies is not supported yet)

Parameters
Name
Description

col

Decimal

Parameter must be a numeric column or an expression that returns a number

currency

Text

Parameter must be a Text value denoting the currency format
Example
sales
34.30
343.50
56.30
Function
Result
to_currency(sales, '$')
$34.30, $343.50, $56.30


To Length

to_length(Column,lengthUnit)

Converts a numeric column to the length datatype

Parameters
Name
Description

Column

Decimal

Parameter must be a numeric value from a column or an expression that returns a number

lengthUnit

Text

Parameter must be a Text value denoting the length unit that the given value needs to be converted into
Example
Column
50
20
30
Function
Result
to_length(Column, 'km')
50km, 20km, 30km


To Temperature

to_temperature(col,tempUnit)

Converts a numeric column to the temperature datatype

Parameters
Name
Description

col

Decimal

Parameter must be a numeric value from a column or an expression that returns a number

tempUnit

Text

Parameter must be a Text value denoting the temperature unit the given value is to be converted into
Example
col
234.3
343.5
56.3
Function
Result
to_temperature(col, 'K')
234.3K, 343.5K, 56.3K


Financial Year

financial_year(date,month)

Returns the financial year from the date specifying the fiscal start month.

Parameters
Name
Description

date

Datetime

Specifies a date column or a datetime column.

month

Number

Specifies the fiscal year starting month.
Example
Data column
09/25/2017
Function
Result
financial_year(Data column, '7')
2018


Quotient

quotient(num1,num2)

Returns the quotient of the given values in integer

Parameters
Name
Description

num1

Number

Parameter must be a Number column or a number, or an expression that returns a number

num2

Number

Parameter must be a Number column or a number, or an expression that returns a number
Example
Function
Result
quotient('12', '4')
3


Lists to Map

lists_to_map(param1,param2)

Returns a Map by combining two Lists.

Parameters
Name
Description

param1

List

Parameter must be of type List or a List column.

param2

List

Parameter must be of type List or a List column.
Example
list1
[20, 30]
list2
[A, B]
Function
Result
lists_to_map(list1, list2)
[20 : A , 30 : B]


Collect List

collect_list(param)

Collects all the values in the column as a list.

Parameters
Name
Description

param

Text

Parameter must be a column conforming to any one data type.
Example
Column
30
50
60
Function
Result
collect_list(Column)
[30, 50, 60], [30, 50, 60], [30, 50, 60]


Keys from Map

keys_from_map(param)

Returns the keys from the map passed as the parameter to the function.

Parameters
Name
Description

param

Map

Parameter must be a column containing maps.
Example
Map Column
key1; value1
key2; value2
key3; value3
Function
Result
keys_from_map(mapColumn)
key1, key2, key3


To Single Line

to_single_line(param)

Returns a text with multiple lines into a single line.

Parameters
Name
Description

param

Text

Parameter must be a Text, or a Text column, or an expression that returns a Text.
Example
Function
Result
to_single_line('Zoho\n\n\nOne\n\n')
Zoho One


Limitations

  1. In the numeric functions, a maximum of 20 whole numbers and 15 decimal places are supported. When your function input exceeds either of these limits, an empty value is returned.

    Zoho DataPrep Personalized Demo

    If you'd like a personalized walk-through of our data preparation tool, please request a demo and we'll be happy to show you how to get the best out of Zoho DataPrep.

    Zoho CRM Training

      Create, share, and deliver

      beautiful slides from anywhere.

      Get Started Now





              Zoho CRM Training Programs

              Learn how to use the best tools for sales force automation and better customer engagement from Zoho's implementation specialists.

              Zoho CRM Training