Prepare function tooltips

ABS

Syntax	Description	Sample usage
(LONG\|DOUBLE\|DECIMAL)	Returns the absolute value of an expression. Note: If the result is not negative (x ≥ 0), the result is returned. If the result is negative (x < 0), the negation of the result is returned.	(-7) will return 7

Syntax

Description

Sample usage

(LONG|DOUBLE|DECIMAL)

Returns the absolute value of an expression.

Note: If the result is not negative (x ≥ 0), the result is returned. If the result is negative (x < 0), the negation of the result is returned.

(-7) will return 7

ACOS

Syntax	Description	Sample usage
(DOUBLE)	Returns the arc cosine of an expression.	(.13) will return 1.440427347091751

Syntax

Description

Sample usage

(DOUBLE)

Returns the arc cosine of an expression.

(.13) will return 1.440427347091751

ASIN

Syntax	Description	Sample usage
(DOUBLE)	Returns the arc sine of an expression.	(.442) will return 0.45782706881105106

Syntax

Description

Sample usage

(DOUBLE)

Returns the arc sine of an expression.

(.442) will return 0.45782706881105106

ATAN

Syntax	Description	Sample usage
(DOUBLE)	Returns the arc tangent of an expression.	(1) will return 0.7853981633974483

Syntax

Description

Sample usage

(DOUBLE)

Returns the arc tangent of an expression.

(1) will return 0.7853981633974483

CBRT

Syntax	Description	Sample usage
(DOUBLE)	Returns the cube root of an expression.	(-8) will return -2

Syntax

Description

Sample usage

(DOUBLE)

Returns the cube root of an expression.

(-8) will return -2

CEIL

Syntax	Description	Sample usage
(DOUBLE)	Returns the value of an expression rounded up to the nearest integer. Note: This function never decreases the result value.	(-3.23) will return -3

Syntax

Description

Sample usage

(DOUBLE)

Returns the value of an expression rounded up to the nearest integer.

Note: This function never decreases the result value.

(-3.23) will return -3

Input/output (CEIL)

x	CEIL (x)
4.6	5
3.5	4
2.4	3
-1.0	-1
-2.4	-2
-3.5	-3

CONCAT

Syntax	Description	Sample usage
(EXPRESSION, EXPRESSION)	Concatenates (chains together) two expressions of identical type. Note: If either expression is null, the resulting expression is null.	('80', '7') will return 807

Syntax

Description

Sample usage

(EXPRESSION, EXPRESSION)

Concatenates (chains together) two expressions of identical type.

Note: If either expression is null, the resulting expression is null.

('80', '7') will return 807

ConvertDdMonYy

Syntax	Description	Sample usage
(STRING)	Converts string representing a date to ISO format.	('25-DEC-17') will return 2018-12-25

ConvertMmDdYyyy

Syntax	Description	Sample usage
(STRING)	Converts string representing a date to ISO format.	('12/25/2018') will return 2018-12-25

ConvertYYYYMMDD

Syntax	Description	Sample usage
(STRING)	Converts string representing a date to ISO format.	('2018/12/25') will return 2018-12-25

COS

Syntax	Description	Sample usage
(DOUBLE)	Returns the cosine of an expression.	(.55) will return 0.8525245220595057

COSH

Syntax	Description	Sample usage
(DOUBLE)	Returns the hyperbolic cosine of an expression.	(4) will return 27.308232836016487

CurrentDateTimeFormatted

Syntax	Description	Sample usage
(STRING)	Returns a string representing the date of when the Pig job started in a format based on the string argument (defined by the user).	('DD/MM/YYYY') will return 12/25/2018

CurrentDateTimeString

Syntax	Description	Sample usage
No arguments required, use ()	Returns a string representing the date of when the Pig job started in a standard ISO format (e.g., 2015-12-14T17:49:11.686-05:00, representing year, month, day, hours, minutes, seconds, milliseconds, and timezone in that order.)	returns 2018-12-25T17:49:11.686-05:00

DateAddDays

Syntax	Description	Sample usage
(STRING, INTEGER, BOOLEAN)	Returns string representing a date after adding specified number of days (could be any +positive or -negative integer) to the given date. Optional boolean variable indicates whether exception or null will be returned for invalid data input; true=exception, false=null	('2018-12-20', 2, true) will return 2018-12-22 or (REMIT_PYMTDATE, 2, true) will return 2018-12-22 where REMIT_PYMTDATE=2018-12-20 for that record.

DateCompare

Syntax	Description	Sample usage
(STRING, STRING, BOOLEAN)	Returns integer indicating whether the second data is equal, earlier, or later than the first date. 0 = equal 1 = stringDate1 is after stringDate2 -1 = stringDate1 is before stringDate2 Optional boolean variable indicates whether exception or null will be returned for invalid data input; true=exception, false=null	('2018-12-21', '2018-12-20') will return 1 ('2018-12-20', '2018-12-20') will return 0 ('2018-12-20', '2018-12-21') will return -1

Syntax

Description

Sample usage

(STRING, STRING, BOOLEAN)

Returns integer indicating whether the second data is equal, earlier, or later than the first date.

0 = equal

1 = stringDate1 is after stringDate2

-1 = stringDate1 is before stringDate2

Optional boolean variable indicates whether exception or null will be returned for invalid data input; true=exception, false=null

('2018-12-21', '2018-12-20') will return 1

('2018-12-20', '2018-12-20') will return 0

('2018-12-20', '2018-12-21') will return -1

DateConvertDdMonYy

Syntax	Description	Sample usage
(STRING)	Converts a string representing a date to ISO format.	('25-DEC-2018') will return 2018-12-25

DateConvertMmDdYyyy

Syntax	Description	Sample usage
(STRING)	Converts a string representing a date to ISO format.	('12/25/2018') will return 2018-12-25

DateConvertYYYYMMDD

Syntax	Description	Sample usage
(STRING)	Converts a string representing a date to ISO format.	('2018/12/25') will return 2018-12-25

DateCurrent

Syntax	Description	Sample usage
No arguments required, use ()	Returns a string representing the current date.	returns 2018-02-11

DateDaysDifference

Syntax	Description	Sample usage
(startDate, endDate)	Returns the number of days between two dates.	('2018-12-20', '2018-12-25') returns 5 ('2018-12-25', '2018-12-20') returns -5 ('2018-01-01', '2018-01-01') returns 0

Syntax

Description

Sample usage

(startDate, endDate)

Returns the number of days between two dates.

('2018-12-20', '2018-12-25') returns 5

('2018-12-25', '2018-12-20') returns -5

('2018-01-01', '2018-01-01') returns 0

DateFirstDayOfMonth

Syntax	Description	Sample usage
(STRING)	Returns the first day of the month for the input string representing a date.	('2018-12-25') will return 2018-12-01

Syntax

Description

Sample usage

(STRING)

Returns the first day of the month for the input string representing a date.

('2018-12-25') will return 2018-12-01

DateGetWeekdayNumber

Syntax	Description	Sample usage
(STRING)	Returns ISO weekDayNumber (Monday = 1, Tuesday=2, and so on..) for string input representing a date.	('2018-12-25') will return 5 (Friday)

DateIsBetweenExclusive

Syntax	Description	Sample usage
(STRING, STRING, STRING, BOOLEAN)	Returns boolean based on whether the first input date string param is between the 2nd and 3rd date, excluding both boundaries. Optional boolean variable indicates the exception or null will be returned for invalid data input; true=exception, false=null	('2018-12-21', '2018-12-20', '2018-12-25') will return true ('2018-12-20', '2018-12-20', '2018-12-25') will return false

Syntax

Description

Sample usage

(STRING, STRING, STRING, BOOLEAN)

Returns boolean based on whether the first input date string param is between the 2nd and 3rd date, excluding both boundaries. Optional boolean variable indicates the exception or null will be returned for invalid data input; true=exception, false=null

('2018-12-21', '2018-12-20', '2018-12-25') will return true

('2018-12-20', '2018-12-20', '2018-12-25') will return false

DateIsBetweenInclusive

Syntax	Description	Sample usage
(STRING, STRING, STRING, BOOLEAN)	Returns boolean based on whether the first input date string param is between the 2nd and 3rd date, including both boundaries. Optional boolean variable indicates the exception or null will be returned for invalid data input; true=exception, false=null	('2018-12-21', '2018-12-20', '2018-12-25') will return true

DateIsEqual

Syntax	Description	Sample usage
(STRING, STRING, BOOLEAN)	Returns boolean stating two input dates are equal or not. Optional boolean variable indicates the exception or null will be returned for invalid data input; true=exception, false=null	('2018-12-21', '2018-12-20') will return false ('2018-12-20', '2018-12-20') will return true

Syntax

Description

Sample usage

(STRING, STRING, BOOLEAN)

Returns boolean stating two input dates are equal or not. Optional boolean variable indicates the exception or null will be returned for invalid data input; true=exception, false=null

('2018-12-21', '2018-12-20') will return false

('2018-12-20', '2018-12-20') will return true

DateIsValidFormat

Syntax	Description	Sample usage
(STRING, STRING, BOOLEAN)	Returns boolean if the specified date input matches the input format, otherwise false. Optional boolean variable indicates the exception or null will be returned for invalid data input; true=exception, false=null	('2018-12-22', 'yyyy-MM-dd') will return true ('2018-12-22', 'yyyy/MM/dd') will return false

Syntax

Description

Sample usage

(STRING, STRING, BOOLEAN)

Returns boolean if the specified date input matches the input format, otherwise false. Optional boolean variable indicates the exception or null will be returned for invalid data input; true=exception, false=null

('2018-12-22', 'yyyy-MM-dd') will return true

('2018-12-22', 'yyyy/MM/dd') will return false

DateParseFormat

Syntax	Description	Sample usage
(STRING, STRING, BOOLEAN)	Returns ISO date for the specified input date string. Input format string param specified by user (provide the format applied to input string representing a date). Optional boolean variable indicates the exception or null will be returned for invalid data input; true=exception, false=null	('2018/12/22', 'yyyy/MM/dd') will return 2018-12-22 ('20181222', 'yyyyMMdd') will return 2018-12-22

Syntax

Description

Sample usage

(STRING, STRING, BOOLEAN)

Returns ISO date for the specified input date string. Input format string param specified by user (provide the format applied to input string representing a date). Optional boolean variable indicates the exception or null will be returned for invalid data input; true=exception, false=null

('2018/12/22', 'yyyy/MM/dd') will return 2018-12-22

('20181222', 'yyyyMMdd') will return 2018-12-22

DateRollBackwardToWeekDayByWeek

Syntax	Description	Sample usage
(STRING, INTEGER, INTEGER, BOOLEAN)	Returns the date based on requested day (backward) of the week of given date. dayNum = > Monday = 1, Tues = 2 and so on...till SunDay = 7. intWeekNum=> lastweek =0, one week before last week = 1, two weeks before last week=2, and so on. Optional boolean variable indicates the exception or null will be returned for invalid data input; true=exception, false=null	('2019-01-11', 3, 1) will return 2019-01-02 (the Wednesday 10 days before)

Syntax

Description

Sample usage

(STRING, INTEGER, INTEGER, BOOLEAN)

Returns the date based on requested day (backward) of the week of given date. dayNum = > Monday = 1, Tues = 2 and so on...till SunDay = 7. intWeekNum=> lastweek =0, one week before last week = 1, two weeks before last week=2, and so on. Optional boolean variable indicates the exception or null will be returned for invalid data input; true=exception, false=null

('2019-01-11', 3, 1) will return 2019-01-02 (the Wednesday 10 days before)

DateRollForwardToWeekDay

Syntax	Description	Sample usage
(STRING, INTEGER, BOOLEAN)	Returns the date based on requested day (forward) of the week of given date.dayNum = > Monday = 1, Tues = 2 and so on...till SunDay = 7. Optional boolean variable indicates the exception or null will be returned for invalid data input; true=exception, false=null	('2019-01-11', 1, false) will return 2019-01-14

DiceCoefficient

Syntax	Description	Sample usage
(STRING, STRING)	Returns the number of tokens (character pairs) common between two strings divided by the total number of tokens.	('frog', 'fog') returns 0.4

DoubleAbs

Syntax	Description	Sample usage
(DOUBLE)	Returns a single numeric value, absolute value of the argument.	(-8.4567890) will return 8.456789

ENDSWITH

Syntax	Description	Sample usage
(STRING, STRING)	Determines if the first argument ends with the string in the second.	(Major, 'y') will return true for fields ending with 'y' (Psychology, 'y') and false for fields that do not end with 'y' (Accounting, 'y')

Syntax

Description

Sample usage

(STRING, STRING)

Determines if the first argument ends with the string in the second.

(Major, 'y') will return true for fields ending with 'y' (Psychology, 'y') and false for fields that do not end with 'y' (Accounting, 'y')

EqualsIgnoreCase

Syntax	Description	Sample usage
BOOLEAN (STRING, STRING)	Determines if two strings are equal (ignoring case).	('field_a', 'Field_b') will return false ('field_a', 'Field_a') will return true

Syntax

Description

Sample usage

BOOLEAN (STRING, STRING)

Determines if two strings are equal (ignoring case).

('field_a', 'Field_b') will return false

('field_a', 'Field_a') will return true

EXP

Syntax	Description	Sample usage
(DOUBLE)	Returns Euler's number e raised to the power of x.	(.5) will return 1.6487212707001282

FLOOR

Syntax	Description	Sample usage
(DOUBLE)	Returns the value of an expression rounded down to the nearest integer. Note: This function never increases the result value.	(-3.23) will return -4 (3.23) will return 3

Syntax

Description

Sample usage

(DOUBLE)

Returns the value of an expression rounded down to the nearest integer.

Note: This function never increases the result value.

(-3.23) will return -4

(3.23) will return 3

Input/output (FLOOR)

x	(x)
4.6	4
3.5	3
2.4	2
1.0	1
-1.0	-1
-2.4	-3
-3.5	-4

INDEXOF

Syntax	Description	Sample usage
(STRING, STRING)	Returns the index of the first occurrence of a character in a string, searching forward from a start index.	('banana' , 'b') will return 0 ('banana' , 'a') will return 1 ('banana' , 'n') will return 2

Syntax

Description

Sample usage

(STRING, STRING)

Returns the index of the first occurrence of a character in a string, searching forward from a start index.

('banana' , 'b') will return 0

('banana' , 'a') will return 1

('banana' , 'n') will return 2

IS_DOUBLE

Syntax	Description	Sample usage
(STRING)	Returns boolean whether or not the input string is a Double.	('5500.00') will return true ('5500') will return true ('JOB_TITLE') will return false

Syntax

Description

Sample usage

(STRING)

Returns boolean whether or not the input string is a Double.

('5500.00') will return true

('5500') will return true

('JOB_TITLE') will return false

IS_INTEGER

Syntax	Description	Sample usage
(STRING)	Returns boolean whether or not the input string is an Integer.	('Billy') will return false ('9') will return true ('9.1') will return false

Syntax

Description

Sample usage

(STRING)

Returns boolean whether or not the input string is an Integer.

('Billy') will return false

('9') will return true

('9.1') will return false

IS_LONG

Syntax	Description	Sample usage
(STRING)	Returns boolean whether or not the input string is type long.	(89745463.9876) will return false (if not stored as a long data type)

IS_NULL

Syntax	Description	Sample usage
(STRING\|LONG\|DOUBLE\|DECIMAL)	Returns boolean whether or not the input string is NULL.	Null fields will return true

IS_NULL_OR_EMPTY

Syntax	Description	Sample usage
(STRING)	Returns boolean whether or not the input string is NULL or EMPTY.	Null or empty fields will return true

LAST_INDEX_OF

Syntax	Description	Sample usage
(STRING, STRING)	Returns the index of the last occurrence of a character in a string, searching backward from the end of the string. The index begins at 0.	('madam', 'm') will return 4

LCFIRST

Syntax	Description	Sample usage
(STRING)	Converts the first character in a string to lower case.	('Marlboro') will return marlboro

LevenshteinDistance

Syntax	Description	Sample usage
LevenshteinDistance	Returns the number of changes needed to change one sequence into another, where each change is a single character modification (deletion, insertion, or substitution)	('frog', 'fog') returns 1

LongestCommonSubsequence

Syntax	Description	Sample usage
(STRING, STRING)	Returns the length of the longest subsequence that two strings have in common. Two strings that are entirely different, return a value of 0, and two strings that return a value of the commonly shared length implies that the strings are completely the same in value and position.	('axbyczqrs', 'abcxyzqtv') returns 4

LOG

Syntax	Description	Sample usage
(DOUBLE)	Returns the natural logarithm (base e) of an expression.	(4) will return 1.3862943611198906

Syntax

Description

Sample usage

(DOUBLE)

Returns the natural logarithm (base e) of an expression.

(4) will return 1.3862943611198906

LOG10

Syntax	Description	Sample usage
(DOUBLE)	Returns the base 10 logarithm of an expression.	(4) will return 0.6020599913279624

LOWER

Syntax	Description	Sample usage
(STRING)	Converts all characters in a string to lower case.	('MARLBORO') will return marlboro

LTRIM

Syntax	Description	Sample usage
(STRING)	Returns a copy of the string with only leading white space removed.	(' Marlboro') will return Marlboro (spaces before the string have been removed)

NextSequence

Syntax	Description	Sample usage
(""schemaName"", "sequenceName")	Returns next value for the desired sequence as per the increment by and last value obtained by select * from podium_core.pd_role_nid_seq; from postgres database.	(""jdbc:postgresql://localhost:5432/podium_md?user=postgres&password=password"", ""pd_role_nid_seq"") will return nextval for seq pd_role_nid_seq

NVL

Syntax	Description	Sample usage
(STRING, STRING)	Returns second string when a null value for first string is encountered.	(Major, 'n/a') will return 'n/a' if the 'Major' field contains a null value. Otherwise, it would return the field value.

OBFUSCATE

Syntax	Description	Sample usage
(STRING\|LONG\|DOUBLE\|BOOLEAN\|DECIMAL, STRING)	Obfuscates a string or number to string value using the obfuscation rule. Obfuscation rules must already be defined in the metadata with the exact rule name. See Obfuscation methods for information on available rules .	('Input') will return ('ObfuscatedInput') dependent on obfuscation rule applied. For example, ("script, 'Digest MD5 Upper x2'") in Transform input field will return obfuscated value like A5F9...

RANDOM

Syntax

Description

Sample usage

no arguments required, use ()

Returns a pseudo random number (DOUBLE) greater than or equal to 0.0 and less than 1.0.

() will return a different pseudo-randomly generated value (greater than or equal to 0.0 and less than 1) for each record in that field(column). These values take the place of the values in that output column for the field in which this function is entered.

example of output values:

0.8448849155781958

0.8179221440864705

0.7165316814860472

REGEX_EXTRACT

Syntax

Description

Sample usage

(STRING, STRING, INTEGER)

Matches regular expression and extracts the matched group defined by index parameter.

(Client, 'e', 0) returns e in those fields with an e present (the first index)

('Client', 'e', 0) returns e (the e in the string 'client')

REPLACE

Syntax

Description

Sample usage

(STRING, STRING, STRING)

Replaces existing characters in a string with new characters.

('marlboro', 'o', 'a') returns marlbara

ROUND

Syntax

Description

Sample usage

(DOUBLE)

Returns the value of an expression rounded to an integer.

(1.02) will return 1

(-3.2) will return 3

Input/Output (ROUND)

x	ROUND (x)
4.6	5
3.5	4
2.4	2
1.0	1
-1.0	-1
-2.4	-2
-3.5	-3
-4.6	-5

ROUND_TO

Syntax	Description	Sample usage
(DOUBLE\|DECIMAL, INTEGER)	Returns the value of an expression rounded to a specified number of decimal digits.	(1234.56, -2) will return 1200.0

RTRIM

Syntax	Description	Sample usage
(STRING)	Returns a copy of a string with only trailing whitespace removed.	('Marlboro ') will return Marlboro (spaces after the string have been removed)

SIN

Syntax	Description	Sample usage
(DOUBLE)	Returns the sine of an expression.	(.75) will return 0.6816387600233341

SINH

Syntax	Description	Sample usage
(DOUBLE)	Returns the hyperbolic sine of an expression.	(.75) will return 0.82231673193583

SIZE

Syntax

Description

Sample usage

(STRING|INTEGER|LONG|DOUBLE)

Computes the number of elements based on any data type.

SIZE includes NULL values in the size computation.

SIZE is not algebraic.

If the tested object is null, the SIZE function returns null.

(Joe) will return 3

(Petunia) will return 7

(00036) will return 1

(1.2679897) will return 1

SPRINTF

Syntax

Description

Sample usage

(FORMAT, STRING, STRING)

Formats string inputs into a new string based on a printf-style template.

('App Version %s %s-%s', 'qdc', TO_STRING(4.2), 'patch') will return "App Version qdc 4.2-patch"

SQRT

Syntax	Description	Sample usage
(DOUBLE)	Returns the positive square root of an expression.	(9) will return 3.0

STARTSWITH

Syntax

Description

Sample usage

(STRING, STRING)

Determines if the first argument starts with the second string.

('llama', 'm') will return false

('marlboro', 'm') will return true

SUBSTRING

Syntax

Description

Sample usage

(SUBSTRING, STARTINDEX, LENGTH)

Returns a subset of the given string.

StartIndex is zero based.

If passed in string is null, UDF returns null.

If startIndex<0 OR startIndex>[stringlength] return null

If length<0 returns null

If length==0 returns empty string

if length>[string length] return till the last character

("abc", 2,1) returns c

("abc",2,0) returns ""

("abc",2,-1) returns NULL

("abc", 2, 5) returns c

(NULL,0,1) returns NULL

("",0,1) returns NULL

TAN

Syntax

Description

Sample usage

(DOUBLE)

Returns the tangent of an expression.

(4) will return 1.1578212823495777

TANH

Syntax

Description

Sample usage

(DOUBLE)

Returns the hyperbolic tangent of an expression.

(4) will return 0.999329299739067

TO_DECIMAL

Syntax	Description	Sample usage
(STRING\|LONG\|DOUBLE)	Casts input to type decimal.	(89745463.9876) will return 89745463.9876

TO_DOUBLE

Syntax	Description	Sample usage
(STRING\|LONG\|DECIMAL)	Casts input to type double.	('15678') will return 15678.0

TO_INTEGER

Syntax	Description	Sample usage
(STRING\|DOUBLE\|DECIMAL)	Casts input to type integer.	(1.9) will return 1

TO_LONG

Syntax	Description	Sample usage
(STRING\|DOUBLE\|DECIMAL)	Casts input to type long.	(89745463.9876) will return 89745463

TO_STRING

Syntax	Description	Sample usage
(LONG\|DOUBLE\|BOOLEAN\|DECIMAL)	Casts input to type string.	(223) will return 223

TRIM

Syntax	Description	Sample usage
(STRING)	Returns a copy of a string with leading and trailing white space removed.	(' Marlboro ') will return Marlboro (spaces before and after string have been removed)

UCFIRST

Syntax	Description	Sample usage
(STRING)	Returns a string with the first character converted to upper case.	('marlboro') will return Marlboro

UniqueID

Syntax

Description

Sample usage

no arguments required, use ()

Returns strings representing field's unique ID for each record. The ID takes form "taskindex-sequence".

() will return ID values ('0-80', '0-71', '0-106')

UPPER

Syntax	Description	Sample usage
(STRING)	Returns a string converted to upper case.	('marlboro') will return MARLBORO

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – please let us know!

Leave your feedback here