Functions

Numeric Functions

ABS(x)

This function returns the absolute (positive) value of a number. Example: ABS(-10)=10

CEILING(x)

This function returns the smallest integer value that is bigger than or equal to a number. Example: CEILING (-10.5)=-10

EXP(x)

This function returns e raised to the power of the specified number.

FLOOR(x)

This function returns the largest integer value that is smaller than or equal to a number. Example: FLOOR(0.5)=0

LOG(x) or LOG(base,x)

This function returns the natural logarithm of a specified number, or the logarithm of the number to the specified base. Example: LOG(20)=2.995732 LOG(20,10)=1.30103

MOD(x,y)

This function returns the remainder of a number divided by another number. Example: MOD(18, 4) returns the remainder of 18/4 = 2

POWER(x,y)

This function returns the value of a number raised to the power of another number. Example: POWER(4, 2) returns 4 raised to the second power (16)

ROUND(x,y)

This function rounds a number to a specified number of decimal places. Example: To round the number to 2 decimal places: ROUND(135.375, 2)=135.38

SQRT(x)

This function returns the square root of a number. Example: SQRT(16)=4

TRUNCATE(x,y)

This function truncates a number to the specified number of decimal places. Example: TRUNCATE(138.375, 2) = 138.37

Date Functions

DayOfMonth(x)

This function returns the day of the month for a given date (a number from 1 to 31). Example: DayOfMonth("date_of_loss") = Accident day

DayOfYear(x)

This function returns the day of the year for a given date (a number from 1 to 366). Example: DAYOFYEAR(“policy_date”)

MONTHOFYEAR(x)

This function returns the month of the year for a given date (a number from 1 to 12). Example: monthOfYear("date_of_loss") = Accident month

MonthOfYearBracket(x)

This function returns the month and the year for a given date (a number with the format YYYYMM). Example: MonthOfYearBracket('31/12/2022')=202212

YEAROF(x)

This function returns the year for a given date (example 2019).

Aggregations Functions

AVG(x)

This function returns the average value of a numeric column.

COALESCE(x,y)

This function replaces the NULL values in a column by with a specific value. Example: coalesce("quota_share",0)

COUNT(x)

This function returns the number of rows that matches a specified criteria.

MAX(x)

This function returns the largest value of the selected column. Example: Max(coalesce("vehicle_value",0))

MIN(x)

This function returns the smallest value of the selected column. Example: MIN(coalesce("vehicle_value",0))

SUM(x)

This function returns the total sum of a numeric column. Example: Sum(coalesce("vehicle_value",0))

String Functions

CONCAT(exp1, exp2,..., expn)

This function adds two or more expressions together. Example: CONCAT("Welcome ", "to ", " Pranalytix!")="“Welcome to Pranalytix!"

LENGTH(x)

This function returns the length of a string (in bytes). Example: LENGTH("Pranalytix")=10

LOWER(x)

This function converts a string to lower-case.

LPAD(x, length, lpad_string)

This function left-pads a string with another string, to a certain length. Example: LPAD("abc/12345",3,"xxx")=abc/12xxx

LTRIM(x)

This function removes leading spaces from a string. Example: LTRIM(" Pranalytix Tutorial")="Pranalytix Tutorial"

This function returns the position of the first occurrence of a sub-string in a string. If the sub-string is not found within the original string, this function returns 0. This function performs a case-insensitive search. Example: Search for "y" in string "Pranalytix.com", and return position: ...

REPLACE(x, FIND, REPL)

This function replaces all occurrences of a sub-string within a string, with a new sub-string. Note: This function performs a case-sensitive replacement. Example: Replace "Pranalitix " with "Pranalytix": REPLACE("Pranalitix Tutorial", "Pranalitix", "Pranalytix")=“Pranalytix Tutorial”