Data Table Processing Functions

Function

Description

Result Type

Backend Support

Frontend Support

addColumns(DataTable table, String format1, String expression1, String format2, String expression2, ...)

Adds one or more columns to the table. Format of the first added column is specified by format1 argument, its value is defined by expression1, etc. Format is encoded into string as specified by format encoding section (visible separators are used). Value for this field is calculated by evaluating the expression that may contain references to the other cells of this table. If row is not specified in the reference, it defaults to the current row (i.e. one for that new field's value is calculated).

Example: addColumns({.:hrStorageTable}, "<usage><S>", "{hrStorageUsed} * 100 / {hrStorageSize} + ' %'") returns a copy of original table with one new column named usage of type String. Values for this column are calculated using {hrStorageUsed} * 100 / {hrStorageSize} + ' %' expression that references two other columns of the same table.

Returns a new DataTable. Does not change the original table.

DataTable

Yes

Yes

addColumnsEx(DataTable table, DataTable columnsToAdd, boolean concurrent)

Similar to the addColumns() function, except the Format and Value of the added columns are specified by a single DataTable argument columnsToAdd.

The columnsToAdd table must contain two fields, Format and Value, which will be used as a format string and value expression.

Format is encoded into string as specified by format encoding.

Value for this field is calculated by evaluating the expression that may contain references to the other cells of this table.

If row is not specified in the reference, it defaults to the current row (i.e. one for that new field's value is calculated).

Each record can be executed concurrently by setting the concurrent argument.

Returns a new DataTable. Does not change the original table.

DataTable

Yes

Yes

addRecords(DataTable table, Object field1, Object field2, ...)

Adds one or more records to the table. If supplied table has N columns, first N field arguments will be used to fill the first added record, second N arguments will fill the second one, and so on. If fewer field arguments are provided than there are columns in the table, the remaining columns in the new record(s) will be populated with their default values.

Returns a new DataTable. Does not change the original table.

DataTable

Yes

Yes

adjustRecordLimits(DataTable table, Integer minRecords, Integer maxRecords)

Adjusts minimum and maximum record number for the table and returns modified table. Additional empty records are added if table has less than minRecords records. Trailing records are removed if table has more than maxRecords records.

Returns a new DataTable. Does not change the original table.

DataTable

Yes

Yes

aggregate(DataTable|String tableOrMask, String expression, Object initialValue)

Calculates an "aggregate value" for a Data Table (if the tableOrMask argument is a Data Table) or a number of contexts (if the first argument is a String representing a context mask). If the first argument is a Data Table, aggregate calculates an expression for every record, making tableOrMask Data Table a default table, and current row a default row. If tableOrMask is a context mask, an expression is calculated for every context which matches the mask, making it a default context.

The expression typically uses two values: the result of the previous calculation, accessible via the {env/previous} reference (see Environment References) and value(s) from the current context or record referred to by a standard reference. During the first calculation, {env/previous} returns the value of the initialValue argument.

Example 1: aggregate("users.*.devices.*", "{env/previous} + ({.:status$connectionStatus} == 1 ? 1 : 0)", 0)

This expression will calculate the number of online ({.:status$connectionStatus} == 1) devices in the system by going over all device contexts (users.*.devices.* mask) and adding 1 to the "aggregate" value if the device is online. The initial value is zero.

Example 2: aggregate({}, '{env/previous} || ({prtCoverStatus} == "open")', false)

In this case, the result is calculated by going over the records of the default Data Table (returned by {} reference). The initial "aggregate" value is Boolean value false. The final result will be true if prtCoverStatus field is equal to open in at least one record, and false otherwise.

Example 3: aggregate({}, 'max({env/previous}, {field})', 0)

The above expression will return maximum value found in the field column.

Example 4: aggregate({}, '({env/previous} * {#row} + {field}) / ({#row} + 1)', 0)

The above expression will calculate an average value of the field column.

Object

Yes

Yes

array(String fieldName [, Object value1, Object value2,…])

Creates a single-column, array-style table from the valueN arguments. The only field of the table will be named fieldName. The field type is determined by the type of the first found non-NULL value argument. If no value is provided, or if all values provided are NULL, then the field type will be String.

Example: array("Integers",1,2,3,4) will create a table with a single field named “Integers” and four rows with the successive values 1, 2, 3, and 4.

DataTable

Yes

No

cell(DataTable table [, String | Integer field [, Integer row [, Boolean description]]])

Returns value contained in the cell of Data Table passed in the first argument. Cell is specified by field and row parameters. If row is not specified, value of field in the first row is returned. If field is a number, it is considered to be field index instead of its name.

If description parameter is specified and is true, the function returns description of cell value instead of the value itself (only if field format specifies selection values).

If field is unspecified, this function returns content of the first cell of the table (e.g. from first field and first row).

Example: cell({users:list()}, "firstname", 5) will return firstname field from 6th row (index = 5) of table containing a list of user accounts (obtained by calling list() context function from Users context).

Object

Yes

Yes

clear(DataTable table)

Removes all records from the table. If minimal record count allowed by the table's format is greater than zero, removes records starting from the end of the table until the minimal record count is reached.

Modifies and returns the original table. Returns a new DataTable in case if the original table is immutable.

DataTable

Yes

Yes

convert(DataTable table, String format [, Boolean useVisibleSeparators)

Converts table to the format specified by format argument. Format is encoded into string as specified by format encoding section. The optional useVisibleSeparators parameter is true by default, indicating that visible separators are used in the format string. If invisible separators are used, useVisibleSeparators should be set to false.

After a new empty table of the specified format is created, the function does its best to copy all data from table to a new table using Data Table smart copy operation.

Example: convert({}, '<<f1><S><A=123>> <<f2><B>>') expression will create a table with String field f1 and Boolean field f2 and copy all data from default table to the newly created table. If default table has field f1 of type Integer, field type will be converted, but all data will be preserved.

Returns a new DataTable. Does not change the original table.

DataTable

Yes

Yes

copy(DataTable source, DataTable target)

Copies as much data as possible from the source table to the target table and returns the target table. Adds/removes records from the target table to match the number of records in the source table (if target table's format allows this). Converts value types wherever necessary.

Modifies and returns the target table. Returns a new DataTable in case if the original target table is immutable.

DataTable

Yes

Yes

count(DataTable | Number value1 [, DataTable | Number value2, ...])

Returns the number of numeric values found within the list of provided tables and values.

Integer

Yes

No

countBlank(DataTable | Number value1 [, DataTable | Number value2, ...])

Returns the number of nulls and empty strings found within the list of provided tables and values.

Integer

Yes

No

countIf(DataTable table1, String condition1 [, DataTable table2, String condition2, ...])

Checks each of the provided tables to count values that match conditions. Returns the total count of matching values. A condition is an expression that may refer to the value being checked using “value” environment variable, i.e. via {env/value} reference.

Integer

Yes

No

decode(String stringToDecode)

Decodes the Data Table from stringToDecode that was encoded according to data table encoding rules.

The function auto-detects table encoding mode accepting tables encoded using both visible and invisible separators.

DataTable

Yes

Yes

describe(DataTable table, String field1, String description1, String field2, String description2, ...)

Changes description of field named field1 to description1, etc. Returns resulting table.

Example: describe({.:ifTable}, "ifDescr", "Name", "ifAdminStatus", "Status", "ifType", "Type", "ifSpeed", "Speed") returns a copy of original table where descriptions of ifDescr, ifAdminStatus, ifType, and ifSpeed fields are changed to Name, Status, Type, and Speed respectively.

Returns a new DataTable. Does not change the original table.

DataTable

Yes

Yes

description(DataTable table)

Returns description of the table, i.e. result of its Naming Expression evaluation.

String

Yes

Yes

distinct(DataTable table)

Removes duplicate records from the table and returns resulting filtered table.

Returns a new DataTable. Does not change the original table.

Data Table

Yes

Yes

encode(DataTable table [, Boolean useVisibleSeparators])

Encodes table into a string according to data table encoding rules.

String

Yes

Yes

filter(DataTable table, String filterExpression)

Returns a table that contains only those rows of source table that match filterExpression.

Example: filter({users:list()}, "contains({firstname}, 'John')") will return only users whose first names contain string John.

Returns a new DataTable. Does not change the original table.

DataTable

Yes

Yes

getFormat(DataTable table [, Boolean useVisibleSeparators])

Returns table format as String. Visibility of separators is specified by useVisibleSeparators parameter. If this parameter is not specified, invisible separators are used.

String

Yes

Yes

getQuality(Data Table table)

Accepts any table as an argument, and returns the Quality property of the argument table, if it exists.

Integer

Yes

Yes

getTimestamp(Data Table table)

Accepts any table as an argument, and returns the Timestamp property of the argument table, if it exists.

Date

Yes

Yes

hasField(DataTable table, String field)

Returns true if table has field named field and false otherwise.

Boolean

Yes

Yes

intersect(DataTable sourceTable, String fieldInSourceTable, DataTable sampleTable, String fieldInSampleTable [, Boolean filterType])

Returns a table containing records that are contained in sourceTable except for ones that were found in sampleTable, if filterType is true.

Returns a table containing records that are contained in sourceTable except for ones that were not found in sampleTable, if filterType is false or undefined.

Records in sourceTable and sampleTable are compared to each other by values of fieldInSourceTable and fieldInSampleTable fields.

Returns a new DataTable. Does not change the original sourceTable.

Data Table

Yes

Yes

join(DataTable left, DataTable right)

Joins two tables horizontally. Length of a resulting table will be a maximum of left and right table lengths. First columns of resulting table will be taken from the left table, and following columns will be taken from the right table.

If the left table has key fields, join function will match records from the right table using those key fields.

Returns a new DataTable. Does not change the original left and right tables.

Data Table

Yes

Yes

match(Object searchKey, DataTable table [, Integer searchType [, Boolean zeroBased]])

Returns the relative position of a searchKey in a single-row or single-column table.

If the searchType is 1, which is the default, assumes that the table is sorted in ascending order and return the largest value less than or equal to searchKey. If the searchType is 0, returns a position of an exact match in an unsorted table. If the searchType is -1, assumes that the range is sorted in descending order and return the smallest value greater than or equal to searchKey.

Returned relative position is one-based by default, but this can be changed by the zeroBased parameter.

Integer

Yes

No

print(DataTable table, String expression, String separator)

Calculates expression for every record of the table and appends output to a resulting string separating results by separator.

Example: print({users:list()}, "{firstname}", ", ") will return of user's first names, e.g. Amanda, Michelle, John, Donald, Paul.

String

Yes

Yes

processBindings(DataTable table)

Executes all data table bindings defined by table's format and returns updated table.

Modifies and returns the original table instance.

DataTable

Yes

No

records(DataTable table)

Returns number of records (rows) in the table.

Integer

Yes

Yes

removeColumns(DataTable table, String column1, String column2, ...)

Removes several columns from the table.

Returns a new DataTable. Does not change the original sourceTable.

DataTable

Yes

Yes

removeRecords(DataTable table, String fieldToCheck, Object value)

Removes specified records from table, i.e. returns a new table containing all records from original one except those having fieldToCheck equal to value.

Returns a new DataTable. Does not change the original sourceTable.

DataTable

Yes

Yes

select(DataTable table, String fieldToSelect, String fieldToCheck, Object value)

Scans table row-by-row and checks if value of fieldToCheck is equal to value. If true, returns value of fieldToSelect from current record. If no matching records found in the table, this function returns NULL.

Example: select({users:list()}, "firstname", "username", "john") will return first name of user with username john, selected from the user stats table (see example above).

Object

Yes

Yes

set(DataTable table, String field, Integer row, Object value)

Changes the value of the table's cell indicated by field and row to value and returns the changed table.

Modifies and returns the table. Returns a new DataTable in case if the original table table is immutable.

DataTable

Yes

Yes

setMultiple(DataTable table, String field, Object value [, String condition])

Scans table row-by-row and sets value to field if condition expression is true. Sets value to field for all rows in table if condition is absent.

Returns a new DataTable.

Does not change the original table.

DataTable

Yes

Yes

setNestedField(DataTable table, Object value, String field1, Integer row1 [, String field2, Integer row2, ...])

Updates a cell of a table's subtable to value. The nested subtable to be updated is pointed by a series of field and row argument pairs.

Modifies and returns the table. Returns a new DataTable in case if the original table is immutable.

DataTable

Yes

No

setQuality(Data Table table, Integer quality)

Sets the Quality property of the provided table to the indicated quality and returns the resulting table. If the provided quality is NULL, the quality property is removed from the table.

Modifies and returns the table. Returns a new DataTable in case if the original table table is immutable.

DataTable

Yes

No

setTimestamp(Data Table table, Date timestamp)

Sets the Timestamp property of the provided table to the indicated timestamp and returns the resulting table. If the provided timestamp is NULL, the timestamp property is removed from the table.

Modifies and returns the table. Returns a new DataTable in case if the original table table is immutable.

DataTable

Yes

No

sort(DataTable table, String field, Boolean ascending)

Sorts table's records according to the natural order of field values. Value of ascending flag specifies sort order.

Returns a new DataTable. Does not change the original table.

DataTable

Yes

Yes

subtable(DataTable table, Integer firstRecord, Integer recordCount, String field1, String field2, ...)

Accepts a table as an argument and returns another table that contains only fields whose names are specified by field arguments. Format and content are preserved in the returned subtable.

If firstRecord is specified (not null), only records starting from zero-based firstRecord index are copied to the resulting table.

If recordCount is specified (not null), it limits the number of records to be added to the resulting table.

Example: subtable({users:list()}, "firstname", "lastname") will return a table with two columns containing first and last names of all Iotellect Server users.

Returns a new DataTable. Does not change the original table.

DataTable

Yes

Yes

structure(String fieldNamePrefix [, Object value1, Object value2,…])

Generates a single-record table, with the number of fields corresponding to the number of valueN arguments. Field names are automatically determined by appending a 1-based index to the fieldNamePrefix string. The data type of each field is determined by the type of the corresponding value argument. If a value argument is NULL, the corresponding field type will be String.

Example: structure("field", "one", 2, 3.14, array("table","chair")) will create a data table with a single row and the following field names and values:

• field1: String-type field with value "one" in the first row

• field2: Long-type field with value 2 in the first row

• field3: Double-type field with value 3.14 in the first row

• field4: DataTable-type field with the data table expressed by array("table","chair") in the first row

DataTable

Yes

No

table([String format [, Object field1, Object field2, ...]])

Creates a new table using format encoded into string and an array of parameters. Rules used to fill table with data are covered here.

The function auto-detects table encoding mode accepting tables encoded using both visible and invisible separators.

Example: table("<<from><I>><<to><I>>", 2, 5, 3, 7) will return a table with two Integer columns and two rows. First row will have from field equal to 2 and to field equal to 5. Second row's values will be from=3 and to=7.

If format is not specified, this function creates a table with an empty format (e.g. with no columns). This table can be than extended using other table processing functions.

DataTable

Yes

Yes

tableFromCSV(String csv, String header, String delimiter [, String format [, String qualifier [, Integer escapeMode [, String comment]]]])

Takes a string CSV document as input and converts it into a Data Table.

The header parameter defines the content of the first line of the CSV file. The value it can take depends on whether or not format is specified.

If format is specified the header can be:

• none
  The CSV file has no header, the first line contains raw data. The data in the first line of the CSV file are imported as the first record of the Data Table and so on.

• skip
  The first line of the CSV file is skipped (considered as non-valuable data).

If format is not specified the first line of the CSV file must contain either the names or the descriptions of Data Table fields. The header in this case can be:

• names
  Data Table field names are imported from the first line of the CSV file. If a name happens to be empty it is replaced with the corresponding column number. The type of Data Table fields is set to String.

• descriptions
  Data Table field descriptions are imported from the first line of the CSV file. The corresponding column numbers are used as Data Table field names. The type of Data Table fields is set to String.

The delimiter parameter specifies the character to use as the column delimiter. It must be a single-character string.

The table format parameter must be a valid Data Table format encoded into a string. The number of columns specified in format must match the number of columns in the CSV file.  

The qualifier parameter indicates the character to use as a text qualifier in the data. Default is double quote (").

The escapeMode parameter allows for the escape character to be specified. The escape character allows for the text qualifier character to be used in the data:

• Use the escape character before the text qualifier to represent an occurrence of the text qualifier in the text.

• Use two instances of the text qualifier to represent an occurrence of the text qualifier.

The comment character allows for a comment character to be specified. Text following the comment character will be ignored until a newline character.

This function works similarly to the CSV Import/Export Options.

DataTable

Yes

Yes

tableFromJSON(String json [, Boolean convertUnequalFieldTypesToString])

Takes a JSON document as an input and converts it into a single-record Data Table where each key-value pair is represented as a separate table field.

If convertUnequalFieldTypesToString parameter is specified and set to true the function converts values of columns to string if values of different types are present in those columns.

DataTable

Yes

Yes

tableToJSON(DataTable table)

Takes a Data Table as an input and converts it JSON document.

String

Yes

Yes

According to the specification, a JSON object is an unordered set of name/value pairs. Thus, any specific order of elements in resulting JSON document is not guaranteed.

union(DataTable first, DataTable second [, DataTable third] [, DataTable …])

Unions two or more tables vertically. Format of the resulting table will be combined from the formats of the input tables. Records of the resulting table will be added in the order of the tables listed in the parameters.

Returns a new DataTable. Does not change the original tables.

DataTable

Yes

Yes

validate(DataTable table [, Boolean throwErrors])

Executes table validators to make sure table data is valid.

If throwErrors parameter is false or missing, returns null in case of success or error text if validation fails.

If throwErrors parameter is true, returns the table itself. Any errors are thrown as a function exception in this case.

Object

Yes

Yes