Inter-record functions

Above() evaluates an expression at a row above the current row within a column segment in a table. The row for which it is calculated depends on the value of offset, if present, the default being the row directly above. For charts other than tables, Above() evaluates for the row above the current row in the chart's straight table equivalent.

above([TOTAL [<fld{,fld}>]] expr [ , offset [,count]])

Below() evaluates an expression at a row below the current row within a column segment in a table. The row for which it is calculated depends on the value of offset, if present, the default being the row directly below. For charts other than tables, Below() evaluates for the row below the current column in the chart's straight table equivalent.

below([TOTAL[<fld{,fld}>]] expression [ , offset [,count ]])

Bottom() evaluates an expression at the last (bottom) row of a column segment in a table. The row for which it is calculated depends on the value of offset, if present, the default being the bottom row. For charts other than tables, the evaluation is made on the last row of the current column in the chart's straight table equivalent.

bottom([TOTAL[<fld{,fld}>]] expr [ , offset [,count ]])

Top() evaluates an expression at the first (top) row of a column segment in a table. The row for which it is calculated depends on the value of offset, if present, the default being the top row. For charts other than tables, theTop() evaluation is made on the first row of the current column in the chart's straight table equivalent.

top([TOTAL [<fld{,fld}>]] expr [ , offset [,count ]])

NoOfRows() returns the number of rows in the current column segment in a table. For bitmap charts, NoOfRows() returns the number of rows in the chart's straight table equivalent.

noofrows([TOTAL])

Column() returns the value found in the column corresponding to ColumnNo in a straight table, disregarding dimensions. For example Column(2) returns the value of the second measure column.

column(ColumnNo)

Dimensionality() returns the number of dimensions for the current row. In the case of pivot tables, the function returns the total number of dimension columns that have non-aggregation content, that is, do not contain partial sums or collapsed aggregates.

dimensionality ( )

SecondaryDimensionality() returns the number of dimension pivot table rows that have non-aggregation content, that is, do not contain partial sums or collapsed aggregates. This function is the equivalent of the dimensionality() function for horizontal pivot table dimensions.

secondarydimensionality ( )

FieldIndex() returns the position of the field value value in the field field_name (by load order).

fieldindex(field_name , value)

FieldValue() returns the value found in position elem_no of the field field_name (by load order).

fieldvalue(field_name , elem_no)

FieldValueCount() is an integer function that returns the number of distinct values in a field.

fieldvaluecount(field_name)

After() returns the value of an expression evaluated with a pivot table's dimension values as they appear in the column after the current column within a row segment in the pivot table.

after([TOTAL] expression [ , offset [,n]])

Before() returns the value of an expression evaluated with a pivot table's dimension values as they appear in the column before the current column within a row segment in the pivot table.

before([TOTAL] expression [ , offset [,n]])

First() returns the value of an expression evaluated with a pivot table's dimension values as they appear in the first column of the current row segment in the pivot table. This function returns NULL in all chart types except pivot tables.

first([TOTAL] expression [ , offset [,n]])

Last() returns the value of an expression evaluated with a pivot table's dimension values as they appear in the last column of the current row segment in the pivot table. This function returns NULL in all chart types except pivot tables.

last([TOTAL] expression [ , offset [,n]])

ColumnNo() returns the number of the current column within the current row segment in a pivot table. The first column is number 1.

columnno([TOTAL])

NoOfColumns() returns the number of columns in the current row segment in a pivot table.

noofcolumns([TOTAL])

Exists() determines whether a specific field value has already been loaded into the field in the data load script. The function returns TRUE or FALSE, so can be used in the where clause of a LOAD statement or an IF statement.

Exists(field_name [, expr])

Lookup() looks into a table that is already loaded and returns the value of field_name corresponding to the first occurrence of the value match_field_value in the field match_field_name. The table can be the current table or another table previously loaded.

LookUp(field_name, match_field_name, match_field_value [, table_name])

Peek() returns the value of a field in a table for a row that has already been loaded. The row number can be specified, as can the table. If no row number is specified, the last previously loaded record will be used.

Peek(field_name[, row_no[, table_name ] ])

Previous() finds the value of the expr expression using data from the previous input record that has not been discarded because of a where clause. In the first record of an internal table, the function will return NULL.

Previous(expr)