Pranalytix | Mojo Helpdesk

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.995732LOG(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)

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

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

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.

COUNT(x)

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

COALESCE(x,y)

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

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

This function right-pads a string with another string, to a certain length.Example:Right-pad the string with "ABC", to a total length of 10: RPAD("Pranalytix Tutorial", 10, "ABC") Gives “ABCABCABCA Tutorial”

LTRIM(x)

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

POSITION(x IN y)

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: POSI...

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”

Functions