Formula Node

Section: General

This section contains basic configuration elements required for any Formula implementation

Field

Requirement

Comments

Node Name

Mandatory

Enter a name for this node.

This provides a handle to which you and others can refer, so it should be simple but meaningful and explain the node’s purpose.

Output Field Name

Mandatory

Provide the new field name to store output data. If a value is used that is the same as the incoming JSON, it will overwrite the original value.

Formula Templates

Optional

A dropdown list with formula templates that can be selected and modified. The selected formula will be added to the formula text field.

Formula

Mandatory

A free text field for entering the required calculations. The list of possible formula node functions can be found below.

Decimal Precision

Optional

Sets the decimal limit for the output value.

Action on Error

Optional

Select from dropdown menu

• Continue - Add Error to JSON
• Continue - Ignore Error
• Stop - Log Error

Output Zero if Value Not Found

Optional

Determines how this node responds to a coding error.
The default will add the error text to the JSON.
If the checkbox is selected, the a 0 will be added as the value in the output field name.

Formula Name (noun.verb.modifier)

Formula Template

(formula with standardized arguments)

Help Text (explanation of function and arguments)

[[ ]]

Takes value from flow field

 [[field_name]]: Get value from field_name key in JSON object

 [[! !]]: Return empty string if value not found

 [[field_name(-1)]]: Get value from LAST incoming JSON

 [[field_name(-1dL)]]: Get last value from previous day

 *Change bold letter to:

 - h: hours

 - m: minutes

 - month: months

 [[field_name(-1dF)]]: Get first value from previous day

 *Change bold letter to:

 - h: hours

 - m: minutes

 - month: months

 [[field_name(same_date)]]: Get value using date-time from current node. Returns value if variable exists in current JSON

 ‘[[field_name]]’: Return value from field_name key in String format

 AGGREGATE Functions of Last Value

 [[field_name<device_group.avg>]]

 [[field_name<device_group.cnt>]]

 [[field_name<device_group.min>]]

 [[field_name<device_group.max>]]

 [[field_name<device_group.sum>]]

CUSTOMTABLE.AGGREGATE

<<custom_table_ID.column_name.aggr>>

Performs a calculation using each value from the specific column name. The available functions are average, sum and count and they are configured using the aggregate field when the custom table column is created. Use aggr as the argument and the formula will automatically use the function that has been configured.

CUSTOMTABLE.GETCUSTOMTABLEDATA

CUSTOMTABLE.LOOKUP

<<custom_table_ID.reference_column_name.target_column_name.field_name>>

Return the value in target_column_name from a custom table, by doing a lookup from the field_name against reference_column_name

DATA.QUERYCASSANDRAHISTORY

Querycassandrahistory('field_name',integer)

Checks a certain number of historical payloads, as configured in the integer argument. Returns True if the value of field_name exists in previous payloads, otherwise returns False.

DEVICE.COUNT

<[CountDevices]>

Returns a count of all devices.

DEVICE.LATITUDE

<[Latitude]>

Returns Latitude from the device configuration.

DEVICE.LONGITUDE

<[Longitude]>

Returns Longitude from the device configuration.

DEVICE.NOW.TZ

<[System.DeviceTZNow]>

Returns current date/time in device timezone.

DEVICE.PAYLOAD.TZ

<[System.DeviceTZTimeStamp]>

Returns payload date/time in device timezone.

LOGIC.IF

If(Expression,Value_Is_True,Value_Is_False)

Returns a value based on a condition.

LOGIC.IN

In(comma_separated_list)

Returns whether an element is in a set of values.

MATH.ABS

Abs(Variable)

Returns the absolute value of a specified number.

MATH.ACOS

Acos(Variable)

Returns the angle whose cosine is the specified number.

MATH.ASIN

Asin(Variable)

Returns the angle whose sine is the specified number.

MATH.ATAN

Atan(Variable)

Returns the angle whose tangent is the specified number.

MATH.CEILING

Ceiling(Variable)

Returns the smallest integer greater than or equal to the specified number.

MATH.COS

Cos(Variable)

Returns the cosine of the specified angle.

MATH.EXP

Exp(Variable)

Returns e raised to the specified power.

MATH.FLOOR

Floor(Variable)

Returns the largest integer less than or equal to the specified number.

MATH.IEEE.REMAINDER

IEEERemainder(Input,Divisor)

Returns the remainder resulting from the division of a specified number by another specified number.

MATH.LOG

Log(Variable, Base)

Returns the logarithm of a specified number.

MATH.LOG10

Log10(Variable)

Returns the base 10 logarithm of a specified number.

MATH.MAX

Max(Variable1, Varable2)

Returns the larger of two specified numbers.

MATH.MIN

Min(Variable1, Variable2)

Returns the smaller of two numbers.

MATH.POW

Pow(Variable,Exponent)

Returns a specified number raised to the specified power.

MATH.RANDOM

Random(D)

Returns random decimal number between 0 and 1.

MATH.RANDOMBETWEEN

Random(From_Integer,To_Integer)

Returns random integer from first variable up to but not including second variable.

MATH.ROUND

Round(Variable,Decimal_Places)

Rounds a value to the nearest integer or specified number of decimal places. The rounding behaviour for a middle number can be changed by using EvaluateOption.RoundAwayFromZero during construction of the Expression object.

MATH.SIGN

Sign(Variable)

Returns a value indicating the sign of a number.

MATH.SIN

Sin(Variable)

Returns the sine of the specified angle.

MATH.SQRT

Sqrt(Variable)

Returns the square root of a specified number.

MATH.TAN

Tan(Variable)

Returns the tangent of the specified angle.

MATH.TRUNCATE

Truncate(Variable)

Calculates the integral part of a number.

REPO.GETDATA

Repo(repo_node_id, 'from_date', 'to_date', 'field_name_to_filter', filter_condition,'field_name_to_do_calculation_on' , AVG)

Function to retrieve data from a repository node. Date formats are 'YYYY-MM-DD HH-MM-SS' Example: REPO(9452,'2019-01-01 00:00:00','2019-05-01 00:00:00','status','on','temp','AVG'). Aggregate functions: MIN,MAX,CNT,SUM,AVG

SYSTEM.DEVICE.TZ.Name

<[System.DeviceTZName]>

Returns Time Zone Name.

SYSTEM.DEVICE.TZ.TIMESTAMP

<[System.DeviceTZTimeStamp]>

Converts into a string (including single quotes) with the current DateTime in the devices time zone (as defined in the device group) in format YYYY-MM-DD HH:MM:SS (can be used with DATEPART function).

SYSTEM.NOW.UTC

<[System.UTCNow]>

Converts into a string (including single quotes) with the current Date-Time (UTC) in format YYYY-MM-DD HH:MM:SS (can be used with DATEPART function).

TAG.CALC.NAME

[[my_value{your_tag.tag_name.avg}]]

Averages the value in 'my_value' field for all devices with a value for the selected tag (sum/cnt/min/max can also be used).

TAG.CALC.VALUE

[[my_value{your_tag.tag_value.avg}]]

Averages the value in 'my_value' field for all devices with the same tag value (sum/cnt/min/max can also be used).

TAG.COUNT.NAME

<[count_devices.your_tag.tag_name]>

Returns a count for all devices with a value for the selected tag.

TAG.COUNT.UNIQUE.VALUE

<[count_unique_tag_values.your_tag]>

Returns an output for each unique tag value accross all devices in the selected tag.

TAG.COUNT.VALUE

<[count_devices.your_tag.tag_value]>

Returns a count for all devices with the same value in the selected tag.

TAG.COUNTVALUES.NAME

Countvalues('field_name To Count',my_tag_name.tag_name')

Returns a count for each number of times each value appeared based on last value received from all devices with the same tag name.

TAG.COUNTVALUES.VALUE

Countvalues('field_name To Count','my_tag_name.tag_value')

Returns a count for each number of times each value appeared based on last value received from all devices with the same tag value.

TAG.VALUE

<[device_tag_value.your_tag]>

Return the tag value set in the device.

TIME.CALCTIMESPAN

Calctimespan('field_name', 'start value', 'end value', bool)

Returns a count in seconds using payload timestamp from the first instance of 'start' value being received, until the first 'end value' is received. If the boolean is true, the output is the time from the last broadcast to now. If false, the output is the time from start to now.

TIME.DATEADD

Dateadd(<[System.UTCNow]>,'DAY',integer)

Function will add the number of time with the second arguement as the unit,from the current time. (can also use minus to remove time). Second argument can receive: SECOND,MINUTE,HOUR,DAY,MONTH,YEAR. Third argument: integer to determine how many seconds, minutes, hours etc. to be added.

TIME.DATEPART

Datepart(datePart,[[Variable]])

Extracts an element from a date string. datePart is the element to extract and accepts YEAR, MONTH, DAY, HOUR, MINUTE,HMS (returns HHmmss), or YMDHMS (returns yyyyMMddHHmmss).

 Variable is a string argument format must be 'yyyy-MM-dd HH:mm:ss'.

TIME.TIMEPART

Timepart(timePart,[[Variable]])

"Extracts an element from a time string. timePart is the element to extract and accepts cepts HOUR, MINUTE, SECOND, HMS (returns HHmmss).

 Variable is a string argument format must be : 'HH:MM:SS'

TIME.TOUNIXTIME

Tounixtime([[Variable]],format)

Converts a date and time to unix time, which is the number of seconds since epoch (1/1/1970).

Variable is the date and time to be converted. format is a string of tokens describing the first argument. Note that this is case sensitive (MM - Month, dd - Day, yyyy - Year, hh - Hour, mm - Minute, ss - Second).

TIME.COUNTDAYSSINCE

Countdayssince(TimeStamp,format)

TIME.TOUNIXTIMEWITHTZ

TOUNIXTIMEWITHTZ('[[Timezone]]','format','[[Date]]','[[Time]]')

Note that this is case sensitive for the Date format (MM - Month, dd - Day, yyyy - Year, hh - Hour, mm - Minute, ss - Second).