Zoomdata Version

# Support for Calculations

Different calculation types support different types of functions and certain data sources support only certain types of functions. To determine what functions you can use in your calculations, refer to the following tables.

 Calculation type Data source type Functions supported Operators All All (Column, Table, Window) All All Derived fields As of Zoomdata 2.6.5 - Impala, PostgreSQL, SAP Hana, ElasticSearch, SparkSQL, Hive on Tez, HDFS, S3, Flat File, Upload API and SQL Server. See table below

## General Operators

#### Mathematical

 Operator Description Supported By: + (ADD) Addition: adds two numbers All data sources - (SUBTRACT) Subtracts one numeric value from another All data sources * (MULTIPLY) Multiply one number by another All data sources / (DIVIDE) Divide one number by another All data sources

#### Logical

 Operator Description Supported By: AND Evaluates to TRUE if both boolean expressions are TRUE All data sources OR Evaluates to TRUE if either boolean expression is TRUE All data sources NOT Reverses the value of any other Boolean operator All data sources IN Evaluates to TRUE if the operand is equal to one of a list of expressions. All data sources BETWEEN Evaluates to TRUE if the operand is within a range All data sources

#### Relational

 Operator Description Supported By: = Checks if the values of two operands are equal or not, if yes then condition becomes true All data sources != Checks if the values of two operands are equal or not- if values are not equal then condition becomes true All data sources > Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true All data sources < Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true All data sources >= Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true All data sources <= Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true All data sources

## Supported Aggregations

Aggregations operate on columns, whole tables, or parts of tables. For information about using aggregations to create custom calculations, see Creating Calculations and Derived Fields.

Supported Aggregations
Function Type Description

SUM ( FieldID ),

TableSUM( FieldID ),

WindowSUM( FieldID, list of attributes )

numeric

Return the sum of included data.

WindowSUM requires a list of one or more attributes to include. Attributes not found in the data set are ignored.

AVG( FieldID ),

TableAVG( FieldID ),

WindowAVG( FieldID, list of attributes )

numeric

Return the average of included data.

WindowSUM requires a list of one or more attributes to include. Attributes not found in the data set are ignored.

MIN( FieldID ),

TableMIN( FieldID ),

WindowMIN( FieldID, list of attributes )

numeric

Return the minimum value in the included data.

WindowSUM requires a list of one or more attributes to include. Attributes not found in the data set are ignored.

MAX( FieldID ),

TableMAX( FieldID ),

WindowMAX( FieldID, list of attributes )

numeric

Return the maximum value in the included data.

WindowSUM requires a list of one or more attributes to include. Attributes not found in the data set are ignored.

COUNT( FieldID ),

TableCOUNT( FieldID ),

WindowCOUNT( FieldID, list of attributes )

any

Return the number of included values. When a FieldID is specified, these functions ignore null values. You can use the wildcard character (*) for FieldID, in which case these functions include null values found in your data.

WindowSUM requires a list of one or more attributes to include. Attributes not found in the data set are ignored.

COUNTD( FieldID ),TableCOUNTD( FieldID ),

WindowCOUNTD( FieldID, list of attributes )

any

Return the number of unique values within the included data.

WindowSUM requires a list of one or more attributes to include. Attributes not found in the data set are ignored.

## Derived Fields

For examples on how and when to use derived fields, see Creating Calculations and Derived Fields.

#### Numerical Functions

 Function Description Example ROUND Rounds a numeric value to the number of decimals specified ROUND (Field_A, 0) FLOOR Returns the largest integer value that is not greater than the passed value FLOOR (value : Numeric) : Numeric CEIL Returns the smallest integer value that is not less than the passed Value CEIL (value : Numeric) : Numeric ABS Returns the absolute, positive value of a given numeric expression ABS (value : Numeric) : Numeric POWER Returns the Base raised to the Exponent~th power POWER (Field_A * 123.45, Field_B / 3) MOD Returns the remainder of a division of dividend by divider MOD (Field_A * 123.45, Field_B / 3) SQRT Returns the non-negative square root of numeric expression SQRT (Field_A * 123.45) EXP Returns the base of the natural logarithm (e) raised to the power of passed numeric expression EXP (Field_A * 123.45) LN Returns the natural logarithm (base e) of the specified numeric expression LN (Field_A * 123.45) NUM_TO_TEXT_ROUNDED Converts the numeric expression rounded to the number of decimals specified to the text NUM_TO_TEXT_ROUNDED (Field_A * 123.45, Field_B / 3 NUM_TO_TEXT Converts the numeric expression to text NUM_TO_TEXT (value : Numeric) UNIX_TIME_TO_TIME Converts the numeric expression to time UNIX_TIME_TO_TIME (Field_Milliseconds / 1000)

#### Text Functions

 Function Description Example SUBSTRING Returns the substring of String value which begins at position defined by Start and is Length characters long. SUBSTRING (Field_A, 4, 3) CONCAT Returns a text that is the result of concatenating two or more Text values CONCAT (Field_FirstName, " ,", Field_LastName) UPPER Returns the argument in uppercase UPPER (SUBSTRING (Field_A, 0, 3 ) LOWER Returns the argument in lowercase LOWER (SUBSTRING (Field_A, 0, 3 )) LTRIM Returns a text value after removing leading blanks LTRIM (SUBSTRING (Field_A, 0, 5)) RTRIM Returns a text value after removing trailing blanks RTRIM (SUBSTRING (Field_A, 0, 5)) LPAD Returns the text argument, left-padded with the text specified by padString to a length of Length characters LPAD (SUBSTRING (Field_A, 0, 15), 3, 'abc') RPAD Returns the Text argument, right-padded with the text specified by padString to a length of Length characters RPAD (SUBSTRING (Field_A, 0, 15), 3, 'abc') REPLACE_ALL Replaces the string with all occurrences of the text specified by fromString replaced by the text defined by toString REPLACE_ALL (SUBSTRING (Field_A, 0, 15), '\$', 'USD') LENGTH Returns the number of characters of the specified string LENGTH (SUBSTRING ("\$12456.00", 2, 10) LOCATE Finds the first occurrence of substring in a string, starting at position LOCATE ('Mr.', CONCAT (Field_FirstName, " ,", Field_LastName), 0) TEXT_TO_TIME Converts the text expression to time according to the specified format TEXT_TO_TIME (Field_A, "YYYY") TEXT_TO_NUM Converts the text string to numeric TEXT_TO_NUM (LTRIM (Field_A, '\$'))

#### Time Functions

 Function Description Example TIME_ADD Adds an interval value to the TimePart of the DateTime TIME_ADD (hour, +7, field_date) EXTRACT Extracts the date part of the time EXTRACT (day_of_week, NOW()) TRUNCATE_TIME Rounds (Truncates) the DateTime value down to the granularity specified by TimePart TRUNCATE_TIME (day, NOW()) TIME_TO_UNIX Returns the value of DateTime as a Unix timestamp TIME_TO_UNIX_TIME (NOW())