Map functions

This section describes the function categories supported by the Map operation. If the input type is shown as Any, the data type can be boolean, float, integer, or string.

For information about how to start a Map operation, see Using the Map function to create new values.

NOTE: Boolean values are treated as integers in a Map function. For example, you can apply the mult function to multiply a Boolean value by a number.

About date and time formats

Some functions require you to enter date or time formats in the input and output fields. You specify year, month, day, hour, minute, and second as %Y, %m, %d, %H, %M, and %S, respectively.

Some specifiers use uppercase and lowercase letters to distinguish between the long and short form of the same name. For example, %b is an abbreviated month name such as Aug, and %B is a full month name such as August.

For a date to be displayed as Sat, April 07, 2007 in a table column, specify the following format as the output format:

%a, %B %d, %Y

For a complete list of specifiers that you can use in a Xcalar function, see the following document:

http://www.cplusplus.com/reference/ctime/strftime/

Arithmetic functions

Function Description Input type Output type
abs Returns the absolute value. Float Float
absInt Returns the absolute value. Integer Integer
add Adds operand 1 and operand 2. Float or integer Float
ceil Returns the smallest integer that is greater than or equal to the input. Float or integer Integer
div Divides operand 1 by operand 2. If operand 2 is 0, the result is FNF. Float or integer Float
exp Raises e (the base of natural logarithms) to the power that is equal to the input. Float or integer Float
floor Returns the largest integer less than or equal to the input. Float or integer Integer
log

Returns the logarithm of the input to the base of e.

IMPORTANT: This function is the natural logarithm. It is not the same as the log function in other applications such as Excel, which uses 10 as default base of the logarithm.
Float or integer Float
log10 Returns the logarithm of the input to the base of 10. Float or integer Float
log2 Returns the logarithm of the input to the base of 2. Float or integer Float
mod Returns the remainder when operand 1 is divided by operand 2. If operand 2 is 0, the result is FNF. Float or integer Float
mult Multiplies operand 1 by operand 2. Float or integer Float
pow Raises operand 1 to the power of operand 2. Float or integer Float
round Rounds the input to the nearest integer. Float or integer Integer
sqrt Returns the square root of the input. Float or integer Float
sub Subtracts operand 2 from operand 1. Float or integer Float

Bitwise functions

Function Description Input type Output type
bitand Returns the result of the AND operation on operand 1 and operand 2. Integer or boolean Integer
bitlshift Returns the result of a logical left shift on the bits of operand 1 by the number of bits specified by operand 2. Integer or boolean Integer
bitor Returns the result of the OR operation on operand 1 and operand 2. Integer or boolean Integer
bitrshift Returns the result of a logical right shift on the bits of operand 1 by the number of bits specified by operand 2. Integer or boolean Integer
bitxor Returns the result of the XOR (exclusive OR) operation on operand 1 and operand 2. Integer or boolean Integer

Conditional functions

Function Description Input type Output type
and Returns the result of the AND operation on operand 1 and operand 2. Boolean Boolean
between Tests if operand 1 is equal to or greater than operand 2, and less than or equal to operand 3. Float or integer Boolean
contains Tests if operand 1 contains operand 2. The pattern matching is case-sensitive by default. If you select the IgnoreCase option (that is, set the option to true), the pattern matching is case-insensitive. String Boolean
eq Tests if operand 1 and operand 2 are equal. Any (but operands must be of the same type) Boolean
exists Tests if a value exists in the input column. Any Boolean
ge Tests if operand 1 is greater than or equal to operand 2. Integer, float, or boolean Boolean
Gt Tests if operand 1 is greater than operand 2. Integer, float, or boolean Boolean
isBoolean Tests if the input data type is boolean. Any Boolean
isFloat Tests if the input data type is float. Any Boolean
isInf Tests if the input value is infinity. Float Boolean
isInt Tests if the input data type is integer. Any Boolean
isStr Tests if the input data type is string. Any Boolean
le Tests if operand 1 is less than or equal to operand 2. Integer, float, or boolean Boolean
like Tests if operand 1 is similar to operand 2. The strings in the operands are case sensitive. The result is true only if the strings are a complete match. You can include an asterisk (*) as a wild card character. For example, if operand 1 is ABC and operand 2 is A*C, the result is true. If operand 1 is ABC and operand 2 is AB, the result is false. String Boolean
lt Tests if operand 1 is less than operand 2. Integer, float, or boolean Boolean
neq Tests if operand 1 is not equal to operand 2. Any (but operands must be of the same type) Boolean
not Returns the logical NOT value of the input. Boolean Boolean
or Returns the result of the OR operation on operand 1 and operand 2. Boolean Boolean
regex Tests if operand 1 matches operand 2, which can be a regular expression. String Boolean

Conversion functions

NOTE: Functions for converting date and time strings require an input or output format. For more information about how to specify the format, see About date and time formats.
Function Description Input type Output type
convertDate Converts the date from one format to another. String String
convertFromUnixTS Converts from a UNIX timestamp to another time format. String String
convertToUnixTS Converts a timestamp to a UNIX timestamp. String String
dateAddDay Adds a specified number of days to a date. The number can be negative. String String
dateAddInterval Adds a time interval (specified in the number of years, the number of months, and the number of days) to a date. The numbers can be negative. String String
dateAddMonth Adds a specified number of months to a date. The number can be negative. String String
dateAddYear Adds a specified number of years to a date. The number can be negative. String String
ipAddrToInt Converts an IPv4 address (with the specified number of octets) to an integer. String Integer
macAddrToInt Converts a MAC address (with the specified number of octets) to an integer. String Integer

Miscellaneous functions

Function Description Input type Output type
dhtHash Generates a hash key used by the distributed hash table. Any Integer
genRandom Generates a random number between the first operand and second operand. Integer or boolean Integer
genUnique Generates a unique integer for each row in a new column. N/A integer

if

Tests if a condition (first argument) is true or false. If true, returns the value of the second argument; if false, returns the value of the third argument.

If the first argument is a non-zero numeric value, it is considered to be true. If it is 0, it is considered to be false.

Boolean Float
ifStr

Tests if a condition (first argument) is true or false. If true, returns the value of the second argument; if false, returns the value of the third argument.

If the first argument is a non-zero numeric value, it is considered to be true. If it is 0, it is considered to be false.

Boolean String
NOTE: For the if or ifStr function, an output value specified in the incorrect data type results in FNF (field not found). For example, for the ifInt function, if you specify the string Correct for a true condition and Incorrect for the false condition, the function returns FNF because the appropriate output data type is integer.

String functions

Function Description Input type Output type
concat Concatenates two strings. String String
countChar Counts occurrences of specified character or string. String Integer
cut

Returns the string located at the numbered field. Field numbers start at 0. Fields are separated by a delimiter. For example, if the string in the Date column is 12/6/2016 and the delimiter is the / character and the field number is 3, the function returns 2016 because it is the third field in the string.

The function does not return a value if the numbered field does not exist, the delimiter does not exist, or the delimiter is a multi-character string.

Input to parse: String

Field number: Integer or boolean

Delimiter: String

String
find

Returns the index of the search string (that is, the position of the string's first character). Starting index and ending index limit where the string can be found. Indexes start at 0 and are inclusive. To return an index as long as it is equal to or greater than the starting index, specify 0 for the ending index. If the string is not found within the specified indexes, the function returns -1.

For example:

For the Date column, you can search for 2007 with the starting index of 3 and ending index of 5. Suppose the source string is 1/1/2007, the function returns 4 because 2007 is found at index 4.

Suppose the source string is 12/12/2007, the function returns -1 because 2007 is not found at an index between 3 and 5. However, if the ending index is set to 0, the function returns 6.

Column to search: String

String to find: String

Starting index: Integer or boolean

Ending index: Integer or boolean

Integer
len Returns the length of the input. String Integer
lower Changes a string to lowercase. String String
replace

Searches for a string and replaces it with another string in a specified column.

The pattern matching is case-sensitive by default. If you select the IgnoreCase option (that is, set the option to true), the pattern matching is case-insensitive.

String String
rfind Same as the find function except that it returns the index of the last character of the search string.

Column to search: String

String to find: String

Starting index: Integer or boolean

Ending index: Integer or boolean

Integer
strip Eliminates the leading and trailing white space from the input string. String String
substring Creates a string from the source that starts at the start index (inclusive) and ends at the end index (exclusive). This is the equivalent of the python command str[startIdx:endIdx]. To have the same effect as str[startIdx:], specify 0 for the end index. To have the same effect as str[:endIdx], specify 0 for the start index. To index back from the end of the string, use a negative index value.

Column to search: String

Start index: Integer or boolean

End index: Integer or boolean

String
upper Changes a string to uppercase. String String
wordCount Returns the number of words in the input. String Integer

Trigonometric functions

Function Description Input type Output type
acos Returns the arccosine of input value, in radians. Integer, float, or boolean Float
acosh Returns the hyperbolic arccosine of input value, in radians. Integer, float, or boolean Float
asin Returns the arcsine of input value, in radians. Integer, float, or boolean Float
asinh Returns the hyperbolic arcsine of input value, in radians. Integer, float, or boolean Float
atan Returns the arctangent of input value, in radians. Integer, float, or boolean Float
atan2 Returns the principal value of arctangent(y/x), in radians. Integer, float, or boolean Float
atanh Returns the hyperbolic arctangent of input value, in radians. Integer, float, or boolean Float
cos Returns the cosine of input value expressed in radians. Integer, float, or boolean Float
cosh Returns the hyperbolic cosine of input value expressed in radians. Integer, float, or boolean Float
degrees Converts input value, expressed in radians, to degrees. Integer, float, or boolean Float
pi Creates a column with the value of pi in each row. N/A Float
radians Converts input value, expressed in degrees, to radians. Integer, float, or boolean Float
sin Returns the sine of input value expressed in radians. Integer, float, or boolean Float
sinh Returns the hyperbolic sine of input value expressed in radians. Integer, float, or boolean Float
tan Returns the tangent of input value expressed in radians. Integer, float, or boolean Float
tanh Returns the hyperbolic tangent of input value expressed in radians. Integer, float, or boolean Float

Type-casting functions

Function Description Input type Output type
bool

Casts value to a boolean value as follows:

  • A string is converted to the boolean value, false. The only exception is that if the string is true, it is converted to the boolean value, true.
  • The number 0 is converted to the boolean value, false.
  • A non-zero float or integer value is converted to the boolean value, true.
Any Boolean
float

Casts value to float.

Any Float
int

Casts value to integer.

Any Integer
string

Casts value to string.

Any String
NOTE: If the input is an empty string, the output of the float and int functions is 0, the output of the bool function is false, and the output of the string function is an empty string.

User-defined functions

User-defined functions (UDFs) are functions that you or other users of Xcalar Design create and upload. Xcalar is shipped with several Xcalar-defined UDFs in the default module. This section describes the Xcalar-defined UDFs.

NOTE: The data type of all UDF output is string.

convertFormats

Syntax Description Input
convertFormats(colName, inputFormat, outputFormat) Converts one time format to another.

colName: name of the column to convert.

inputFormat: format of the column named colName. For example, if the format is 2005-12-05 in the column, then the input format is %Y-%m-%d.

outputFormat: output format. For example if you want the date to be 12/05/2005, then the output format is %m/%d/%Y.

convertFromUnixTS

Syntax Description Input
convertFromUnixTS(colName, outputFormat) Converts from a UNIX timestamp to another time format.

colName: name of the column to convert.

outputFormat: format to which the column is converted.

convertToUnixTS

Syntax Description Input
convertToUnixTS(colName, inputFormat) Converts from a timestamp format to the UNIX timestamp format.

colName: name of the column to convert.

inputFormat: format from which the column is converted.

openExcel

This function is not to be invoked by Xcalar users. It is called by Xcalar Design when you select the file format to be Excel during the dataset creation step.

convertExcelTime

Syntax Description Input
convertExcelTime(colName, outputFormat) In Excel, a date can be displayed and stored as a value that represents the number of days that have elapsed since the base date. For example, a date shown as 48739 means that 48739 days have elapsed since the base date. This function converts this value to a regular date.

colName: name of the column to convert.

outputFormat: output format from which the Excel date is converted. For example, if you want the date to be 2005-12-04, then enter %Y-%m-%d.

splitWithDelim

Syntax Description Input
splitWithDelim(txt, index, delim) Extracts the substring from a string starting from the character following the delimiter with the specified index. For example, it there are two delimiters, the indexes for the delimiters are 0 and 1. To obtain the substring starting from the character following the second delimiter, specify 1 as the index.

txt: string from which a substring is extracted.

index: index of the delimiter. Indexing starts from 0, and the substring starts from the character following the delimiter pointed to by the index.

delim: character that serves as delimiter.

The following examples show how this function is used:

splitWithDelim("a-b-c", "-", 1) produces the substring b-c.

splitWithDelim("a-b-c", "-", 3) produces an empty substring.

now

This function gets the current time of the Xcalar cluster. The output represents the number of seconds since the epoch, in UTC.

This function does not take any input.

Go to top