Add/Edit
Functions
It is possible to perform
calculations on your data before it displayed in Enterprise RTView. For
example, you can create a function that will take the average value of
a table column or add the values of multiple data attachments. To display
the results, attach your functions to objects using the
Attach
to Function Data dialog. NOTE: Functions are only available for
use within the display where they are created. The file functions.rtv
(located
in the tutorials directory) is provided as an example of the different
ways that functions can be utilized in a display.
Select Tools>Functions
to open the Functions window. To create a function, click on the Add button.
Select an existing function and click on Edit to modify, Copy to
duplicate or Remove to delete.
Functions associated with global functions or
included display files can only be modified from the display in which they were
originally defined. NOTE: If a global function shares the same name as a local
function, only the local function will be displayed and the global function will
be ignored.
Functions listed can be sorted by any of
the following by clicking on the column name:
| Column Name |
Description |
| Name |
Name of the function. |
| Local |
Selected if the function is defined in the
current display. |
| Public |
Selected if the function is available in
display (.rtv) files, other than the display where it was defined. |
| Source |
Name of the file in which a global function
or included display file is defined. |
| Uses |
Number of times the function is used in the
current display. |
| Description |
Description entered in the Edit Function
dialog. |
Create
a Function
Begin by entering a name
for your function. Each function must have a unique name (within that display)
that does not contain spaces. NOTE: The name "function" is not allowed.
If the Function Name specified will be
defined as a global function or
in an included display file, then
selecting the Public checkbox will make this function available in all displays. When the Public checkbox is deselected, this function will only be
available for data attachments in the display (.rtv) file where it was defined.
Deselect the Public checkbox if the function you have defined may generate
intermediary results that are only useful in the context of the current display
and would be confusing or unusable during the creation of other displays.
The Function Type drop down menu lists all available
calculations. Enterprise RTView comes with an array of built-in, pre-defined
Function Types or you can create your own
Custom Functions. Argument fields populate based on the selected Function Type.
You may either enter a value into the Argument field or right-click to select
Attach
to Data and choose a data source. You may include a description of any length, which
will also be visible in the
Attach
to Function Data dialog.
An argument that has been
attached to data is displayed in green, this indicates that editing is
no longer possible in the Argument field. Double-click to edit the data
attachment. Right-click and select Attach to Data to change the
data source. Select Detach from Data to remove the attachment.
Function Types
Functions are separated
into two groups according to whether they act on Scalar or Tabular data.
Scalar Functions
| Function
Type |
Description |
Arguments
|
Return
Type |
|
Name
|
Type
|
| Add |
Adds
the two arguments. |
Argument1 |
Numeric |
Numeric |
| Argument2 |
Numeric |
| Average |
Calculates average of the two arguments. |
Argument1 |
Numeric |
Numeric |
| Argument2 |
Numeric |
| Concatenate |
Combines
the two Values into a single text string. |
Value1 |
Numeric or Text |
Text |
| Value2 |
Numeric or Text |
| Date
Add |
The
Date Add function adds the specified Number of Date Part intervals, which
may be negative, to the specified Date and returns a string representing
the resulting date/time. The Date must be either a formatted date/time
string or a Java standard date/time argument in milliseconds from Jan 1,
1970. For Date Part, specify s, m, h, d,
w, M, q or y for seconds, minutes, hours, days, weeks,
months, quarters or years. The Date Format specified is the same as the
Java SimpleDateFormat class. For example, the format MMMM dd,
yyyy hh:mm:ss a would result in the form August 30, 2003 05:32:12
PM. If no Date Format is given, the string is returned in the form
08/30/03
05:32 PM. Use q, qqq or qqqq for short, medium
or long versions of quarter notation. For example, qqq-yyyy will
result in a string of the form Qtr 1-2005. This function returns
a text string. |
Date |
Text (Java standard
Date/Time argument in milliseconds) |
Text |
| Number |
Text (e.g., MMMM
dd, yyyy hh:mm:ss a) |
| Date Part |
Text (e.g., s,
m, h, d, w, M, q, y) |
| Date Format |
Text (e.g., MMMM
dd, yyyy hh:mm:ss a) |
| Date
Ceiling |
The
Date Ceiling function determines which Date Part interval contains the
Date, and returns a string representing the start value of the next Date
Part interval. The Date must be either a formatted date/time string or
a Java standard date/time argument in milliseconds from Jan 1, 1970. For
Date Part, specify s, m, h, d, w,
M, q or y for seconds, minutes, hours, days, weeks, months,
quarters or years. The Date Format specified is the same as the Java SimpleDateFormat
class. For example, the format
MMMM dd, yyyy hh:mm:ss a would
result in the form August 30, 2003 05:00:00 PM. If no Date Format
is given, the string is returned in the form 08/30/03 05:00 PM.
Use q, qqq or qqqq for short, medium or long versions
of quarter notation. For example, qqq-yyyy will result in a string
of the form Qtr 1-2005. This function returns a text string. |
Date |
Text (Java standard
Date/Time argument in milliseconds) |
Text |
| Date Part |
Text (e.g., s,
m, h, d, w, M, q, y) |
| Date Format |
Text (e.g., MMMM
dd, yyyy hh:mm:ss a) |
| Date
Compare |
The
Date Compare function compares Date 1 and Date 2 and returns -1 if Date
1 is less than Date 2, 0 if the values are equal and 1 if Date 1 is greater
than Date 2. Date 1 and Date 2 must be either formatted date/time strings
or Java standard date/time arguments in milliseconds from Jan 1, 1970.
For Date Part, specify s, m, h, d, w,
M,
q
or y for seconds, minutes, hours, days, weeks, months, quarters
or years. Date Part controls the resolution of the comparison. For example,
comparing 08/30/03 05:32 PM to 08/30/03 04:47 PM with Date
Part set to m will return 1, while setting Date Part to d
will cause this function to return 0. This function returns a number. |
Date 1 |
Text (Java standard
Date/Time argument in milliseconds) |
Numeric |
| Date 2 |
Text (Java standard
Date/Time argument in milliseconds) |
| Date Part |
Text (e.g., s,
m, h, d, w, M, q, y) |
| Date
Difference |
The
Date Difference function calculates the integer number of Date Part intervals
by which Date 1 is less than Date 2. Date 1 and Date 2 must be either formatted
date/time strings or Java standard date/time arguments in milliseconds
from Jan 1, 1970. For Date Part, specify s, m, h,
d,
w,
M,
q
or y for seconds, minutes, hours, days, weeks, months, quarters
or years. For example, comparing 05/12/05 05:32 PM to 05/15/05
04:47 PM with Date Part set to d will return
3, This
function returns a number. |
Date 1 |
Text (Java standard
Date/Time argument in milliseconds) |
Numeric |
| Date 2 |
Text (Java standard
Date/Time argument in milliseconds) |
| Date Part |
Text (e.g., s,
m, h, d, w, M, q, y) |
| Date
Floor |
The
Date Floor function determines which Date Part interval contains the Date,
and returns a string representing the starting date/time value of that
interval. The Date must either be a formatted date/time string or a Java
standard date/time argument in milliseconds from Jan 1, 1970, For Date
Part, specify s, m, h, d, w, M,
q
or y for seconds, minutes, hours, days, weeks, months, quarters
or years. The Date Format specified is the same as the Java SimpleDateFormat
class. For example, the format MMMM dd, yyyy hh:mm:ss a would result
in the form August 30, 2003 05:00:00 PM. If no Date Format is given,
the string is returned in the form
08/30/03 05:00 PM. Use q,
qqq
or qqqq for short, medium or long versions of quarter notation.
For example, qqq-yyyy will result in a string of the form
Qtr
1-2005. This function returns a text string. |
Date |
Text (Java standard
Date/Time argument in milliseconds) |
Text |
| Date Part |
Text (e.g., s,
m, h, d, w, M, q, y) |
| Date Format |
Text (e.g., MMMM
dd, yyyy hh:mm:ss a) |
| Date
Format |
The
Date Format function returns a formatted string representing the specified
Date. The Date must be a Java standard date/time argument in milliseconds
from Jan 1, 1970. The Date Format specified is the same as the Java SimpleDateFormat
class.
For example, the format MMMM
dd, yyyy hh:mm:ss a would result in the form August 30, 2003 05:32:12
PM. If no Date Format is given, the string is returned in the form
08/30/03
05:32 PM. This function returns a text string. |
Date |
Text (Java standard
Date/Time argument in milliseconds) |
Text |
| Date Format |
Text (e.g., MMMM
dd, yyyy hh:mm:ss a) |
| Date Now |
The Date Now
function returns a string representing the current date and time.
The Date Format specified is the same as the Java SimpleDateFormat class.
For example, the format MMMM dd, yyyy hh:mm:ss a would result in
the form August 30, 2003 05:32:12 PM. If no Date Format is given,
the
string is returned in the form 08/30/03 05:32 PM. This function
returns a text string. |
Date Format |
Text (e.g., MMMM
dd, yyyy hh:mm:ss a) |
Text |
| Delta |
Calculates the
rate of change of the Value over the Time Interval specified in seconds.
If no Time Interval is given, the absolute delta is returned. |
Value |
Numeric |
Numeric |
| Interval |
Numeric |
| Divide |
Divides
the first argument by the second. |
Argument1 |
Numeric |
Numeric |
| Argument2 |
Numeric |
| Duration |
Takes a Duration
argument in milliseconds and returns a text string representing that Duration.
If no Duration Format is specified, the string is returned in the form
"15:32" (hours:minutes). The Duration Format may contain any of the characters
d,
s,
or . indicating that days, seconds, or milliseconds are to be included
in addition to hours and minutes in the returned string. |
Duration |
Text |
Text |
| Duration
Format |
Text (e.g., ds) |
| Format
Number |
The Format Number
function applies the specified Format to the Number To Format and returns
a formatted string. The Format can be specified as a Java format specification,
or with the following shorthand: $ for US dollar money values, $$ for US
dollar money values with additional formatting, or () for non-money values,
formatted similar to money. For example, if Number To Format is 50,
and Format is $ this function would return $50.00. Both positive and negative
formats can be supplied, for example: #,###;(#,###). |
Number
to Format |
Numeric |
Numeric |
| Format |
Text (e.g., #,###;(#,###)) |
| Get Substitution |
Returns the current
value of the given Substitution String. |
Substitution
String |
Text |
Text |
| isWindowsOS |
The isWindowsOS
function returns 1 if Enterprise RTView is not running in an applet and
the operating system it is running on is Windows, otherwise it returns
0. This function returns a number. |
Not applicable |
Not applicable |
Number |
| Max |
Returns larger of the two arguments. |
Argument1 |
Numeric |
Numeric |
| Argument2 |
Numeric |
| Min |
Returns smaller of the two arguments. |
Argument1 |
Numeric |
Numeric |
| Argument2 |
Numeric |
| Modulo |
Divides
the Value by the Divisor and returns the remainder. |
Value |
Numeric |
Numeric |
| Divisor |
Numeric |
| Multiply |
Multiplies
the two arguments. |
Argument1 |
Numeric |
Numeric |
| Argument2 |
Numeric |
| Percent |
Computes
the percentage of the Value, given the range defined by Min Value and Max
Value. This function returns a number between 0 and 100. |
Value |
Numeric |
Numeric |
| Min Value |
Numeric |
| Max Value |
Numeric |
| Replace
Value |
Replaces the
Value with associated text from Replacement Values. Replacement Values
should contain pairs of values and replacement values separated by a :.
If the names of values or replacement values listed in Replacement Values
contain a space or a colon, they must be enclosed in single quotes.
For example, if the Value
is Windows NT and pairs listed in Replacement Values are 'Windows
NT':winnt Windows2000:win2k, the text string returned will be winnt.
If Return Value if No Match
is set to 1, then the returned text will be the Value if no match.
If Return Value if No Match is set to 0, then an empty text string
will be returned if no match. |
Value |
Text |
Text |
| Replacement Values
(e.g., WindowsNT:winnt) |
Text |
| Return Value
if No Match |
Numeric |
| Set
Substitution |
Sets
the Substitution String to the given Value. This function returns the value
that has been set. |
Substitution
String |
Text |
Text |
| Value |
Numeric or Text |
| Set
Substitutions By Lookup |
The
Set Substitutions By Lookup function sets multiple substitutions based
on the values in the Lookup Table. The Key is compared against the values
in the first column of the Lookup Table to determine which row to use to
set substitution values. For each additional column in the Lookup Table where the column name starts with $,
a substitution will be set on the display, where the substitution name
is the name of the column and the substitution value is the value from
that column in the row where the first column matched the Key. |
Key |
Numeric or Text |
Text |
| Lookup Table |
Table |
| Subtract |
Subtracts
the second argument from the first. |
Argument1 |
Numeric |
Numeric |
| Argument2 |
Numeric |
Tabular Functions
|
Function
Type |
Description |
Arguments
|
Return
Type |
|
Name
|
Type
|
| Add All Rows Or Columns |
The Add All
Rows Or Columns function calculates the sum within each row or column of the
specified Table.
All numerical columns will be included in the
calculation.
Result Column - Controls whether to return a column or a row of result values.
To get a column of result values, one value for each row, set Return Column to
1. To get a row of result values, one value for each column, set Return
Column to 0.
Result Label - Used to specify a label for the result row or column. To have the
Result Label placed in a particular column (when Return Column is 0),
specify a column name in Result Label Column. |
Table |
Table |
Table |
| Return Column |
Numeric |
| Result Label |
Text for Column or Row
Label
Default label is Total |
| Result Label
Column |
Name of Table
Column |
|
Add Columns |
The Add
Columns function calculates the sum of the specified columns within the specified
Table.
NOTE: If a specified column is not found, the function attempts to convert the
string argument to a numeric value. If this succeeds, the value is used as a
constant in each row-wise operation. |
Table |
Table |
Table |
| First Column Name or
Numeric Value |
Text or Numeric |
| Second Column Name or
Numeric Value |
Text or Numeric |
| Result Column Name |
Name of Table Column |
|
Average All Rows Or Columns |
The
Average All Rows Or Columns function calculates the average within each row or column of the
specified Table.
All numerical columns will be included in the
calculation.
Result Column - Controls whether to return a column or a row of result values.
To get a column of result values, one value for each row, set Return Column to
1. To get a row of result values, one value for each column, set Return
Column to 0.
Result Label - Used to specify a label for the result row or column. To have the
Result Label placed in a particular column (when Return Column is 0),
specify a column name in Result Label Column. |
Table |
Table |
Table |
| Return Column |
Numeric |
| Result Label |
Text for Column
or Row Label Default label is Average |
| Result Label
Column |
Name of Table
Column |
| Average
Columns |
The Average
Columns function calculates the average of the specified columns within the specified
Table. NOTE: If a specified column is not
found, the function attempts to convert the string argument to a numeric value.
If this succeeds, the value is used as a constant in each row-wise operation. |
Table |
Table |
Table |
| First Column Name or
Numeric Value |
Text or Numeric |
| Second Column Name or
Numeric Value |
Text or Numeric |
| Result Column Name |
Name of Table Column |
Baseline Over
Time |
The Baseline
Over Time function calculates a baseline average of the values in the specified
Table over the number of Date Part Intervals and offsets the timestamp to a
Reference Time. The Table must
contain a time column and a number column.
This function returns a table containing
a baseline calculated for the Number of Intervals specified or for all of the
data in the Table if the Number of Intervals is 0.
Date Part - The date unit to use. Enter
s, m, h, d, w, M, q or y, for seconds, minutes, hours, days, weeks, months,
quarters or years. If left blank, the Date Part will default to seconds.
Reference Time - After the baseline
average has been calculated over the range of data specified, all values in the
resulting time column are offset to start at the given Reference Time. This
provides an easy way for the baseline to be plotted in a trend graph against a
current set of values. |
Table |
Table |
Table |
| Date Part |
Text (e.g., s, m, h, d, w,
M, q, y) |
| Date Parts per Interval |
Numeric |
| Number of Intervals |
Numeric |
| Reference Time |
Text |
| Combine |
The
Combine function combines Table 1 and Table 2 into a single table. When
Combine Rows is 0, the combined table will contain the columns from
Table 1 followed by the columns from Table 2. When Combine Rows is 1,
the combined table will contain the rows from Table 1 followed by the rows
from Table 2. In addition, when Combine Rows is 1, set Ignore Column
Names to 0 to reorder the columns of Table 2 so its column names
match Table 1 before merging rows. Set Ignore Column Names to 1
to merge rows without trying to match columns by name. |
Table
1 |
Table |
Table |
| Table 2 |
Table |
| Combine Rows |
Numeric |
| Ignore
Column Names |
Numeric |
| Convert
Columns |
The Convert
Columns function converts the specified columns to the specified type and replaces the input columns with the results.
Specify a single column name or a semi-colon (;) delimited list of column names for Columns to Convert.
When converting from numeric data (other than Long) to the date-time (Time)
type, columns are first converted to Long data and then
to Time data. If a String column entry cannot be parsed as a date,
then the resulting entry will be blank.
NOTE: Convert to Type options (Boolean, Integer,
Long, Float, Double, String or Time) may be abbreviated to the first letter. |
Table |
Table |
Table |
| Columns to Convert |
Text |
| Convert to Type |
Text (Boolean,
Integer, Long, Float, Double, String or Time.) |
| Copy |
Copies the specified
Table. |
Table |
Table |
Table |
| Count |
Counts rows in
the specified Table Column. |
Table Column |
Table Column |
Numeric |
| Count
By Bands |
The Count By Bands function divides the range given by the Min Value and Max Value into bands and counts the number of rows in the Table argument that contain a value that lies within each band.
This function accepts as arguments a Min Value, Max Value and Number of Bands and returns a table containing one column that holds the midpoint values of each band (one row for each band), and N additional columns, one for each column that was contained in the
specified Table. These columns contain the total counts of values that fall within the calculated bands. If the Return Cumulative Percents argument is set to true (1) then the returned columns will contain the cumulative percentage of the total count in each cell, rather than the
individual counts. |
Table |
Table |
Table |
| Number of
Bands |
Numeric |
| Include
Min/Max |
Numeric |
| Min Value |
Numeric |
| Max Value |
Numeric |
| Return
Cumulative Percent |
Numeric |
| Count
Unique Values |
Returns
a table listing unique values and their counts from the specified Table
Column.
To include rows in the returned
table for values that are not always present in the Table Column, specify
a table column in Value List that contains all possible values. If the
Value List is not specified, all unique values from Table Column will be
included in the returned table.
If Restrict to Value List
is set to
0, all unique values from the Table Column will be included
in the returned table. If Restrict to Value List is set to 1 and
Value List is specified, only rows from the Value List will be included.
If Use Column Names is set to 1, then
original column names will be retained. If set to 0, columns will be given
generic names (e.g.: Subtotal1, Total1, etc.). Generic column names are useful
when the data attachment for the Table argument uses a substitution that will
cause the column names to change when the substitution changes. |
Table Column |
Table Column |
Table |
| Value List |
Table Column |
| Restrict to Value
List |
Numeric |
| Use Column Names |
Numeric |
| Count
Unique Values By Time |
The
Count Unique Values By Time function returns a table listing unique values
and their counts from a specified Table, sorted by the number of Date Part
Intervals. The Table must contain a time and a value column. The returned
table contains an interval column, a column for each unique value, and
counts for Number of Intervals specified or for all data in the Table if
the Number of Intervals is 0. For Date Part, specify s, m,
h,
d,
w,
M,
q
or y for seconds, minutes, hours, days, weeks, months, quarters
or years. The optional Date Format argument controls the format in the
returned table using the Java SimpleDateFormat class. Use
q,
qqq
or qqqq for short, medium or long versions of quarter notation.
To include columns in the returned table for values not always present
in the value column, specify a table column in the Value List argument
containing all possible values. If Restrict to Value List is 0,
or if the Value List is not specified, all unique values from the value
column are included in the returned table, otherwise only values from the
Value List are included.
If Use Column Names is set to 1, then original column names will be retained. If
set to 0, columns will be given generic names (e.g.: Subtotal1, Total1, etc.).
Generic column names are useful when the data attachment for the Table argument
uses a substitution that will cause the column names to change when the
substitution changes. |
Table |
Table |
Table |
| Date Parts Per Interval |
Numeric |
| Number Of Intervals |
Numeric |
| Date Part |
Text (e.g., s,
m, h, d, w, M, q, y) |
| Date Format |
Text (e.g., MMMM
dd, yyyy hh:mm:ss a) |
| Value List |
Table Column |
| Restrict to Value
List |
Numeric |
| Use Column Names |
Numeric |
| Delta Rows |
The Delta Rows function computes the delta between
incoming rows of tabular data.
The Delta Rows function returns a table
including, for the specified columns, new values for the difference between this
update and the previous.
Delta Column Names - The name of one or more columns for which deltas will be
calculated. This field cannot be left blank.
Index Column Names - The name of one or more columns that uniquely identify a
row in the table. If left blank, the default is to calculate deltas for all rows
as if they had the same value. The values contained in each index column are
concatenated to form a unique index used to organize the resulting summary data.
Replace Data With Deltas - If set to 1, delta values will replace
original values in the same column of the specified Table. If set to 0,
new columns will be added. The new columns will use the delta columns names with Delta prefixed.
Display Negative Values - If set to 1, delta values less than zero will
be displayed with a negative sign. If set to 0, they will be displayed as
zero. |
Table |
Table |
Table |
| Delta Column Names |
Name of Table
Column |
| Index Column Names |
Name of Table
Column |
| Replace Data with Deltas |
Numeric |
| Display Negative Values |
Numeric |
| Divide
Columns |
The Divide
Columns function divides the value of each row in the second column into the
corresponding rows in first column of the specified Table.
NOTE: If a specified column is not found, the
function attempts to convert the string argument to a numeric value. If this
succeeds, the value is used as a constant in each row-wise operation. |
Table |
Table |
Table |
| First Column Name or
Numeric Value |
Text or Numeric |
| Second Column Name or
Numeric Value |
Text or Numeric |
| Result Column Name |
Name of Table Column |
| Filter
And Extract Matches |
The Filter And Extract Matches function returns a table containing all rows from the
specified Table in which the value of a column matches a pattern. For each matching row, each token from the specified column that matches a group in the pattern is extracted to a new column. The column to which the pattern is to be applied is specified in Filter Column Name, or if left blank, the pattern is applied to the row name. If Pattern Is Reg Expr is zero (the default), then Pattern should contain a string where * is a wildcard. If Pattern Is Reg Expr is nonzero then Pattern should contain a regular expression as described at:
http://java.sun.com/j2se/1.5/docs/api/java/util/regex/Pattern.html
Number of New Columns specifies the number of new columns that should be added to the result table to contain the matching groups extracted from the filter column. New Column Names specifies the names for each of the new columns, separated by semicolon.
For example, if Number of New Columns is 2 and New Column Names is FirstName;LastName then the result table will contain 2 additional columns named FirstName and LastName, after all the columns from the input table. Furthermore, if Filter Column Name is CustomerName and Pattern is * * and PatternIsRegExpr is zero, then if a row in the input table contains Joe Smith in the CustomerName column, the corresponding row in the result table will contain Joe in the FirstName column and Smith in the LastName column.
|
Table |
Table |
Table |
| Filter Column |
Name of Table
Column |
| Pattern |
Text |
| Pattern is a Regular
Expression |
Numeric |
| Number of New
Columns |
Numeric |
| New Column
Names |
Text |
| Filter By
Pattern |
The Filter By
Pattern function returns a table containing all rows from the specified Table in
which the value of a column matches a pattern.
Filter Column Name - A string that
specifies the name of the column in the input table to which the pattern is
applied. If this argument is blank, then the pattern is applied to the row name.
Pattern - A string that specifies a
pattern. For each row in the Table, if the value in the specified column matches
the pattern then the row is included in the result table.
Pattern Is Regular Expression - If 0
(the default), then Pattern should contain a string where * is a wildcard that
represents any character, including none. If nonzero then Pattern must contain a
regular expression as described at:
http://java.sun.com/j2se/1.5/docs/api/java/util/regex/Pattern.html
|
Table |
Table |
Table |
| Filter Column |
Name of Table
Column |
| Pattern |
Text |
| Pattern is a Regular
Expression |
Numeric |
| Filter By
Row |
Returns
a copy of the specified Table, containing only those rows in which the
value of the Filter Column exactly matches the Filter Value. Enter * for
Filter Value to display all rows in the table or "*" to use * as a literal
comparative value. To list multiple values, separate with a semicolon.
For example:
value1;value2;value3. If your value contains
a semicolon, enclose it in single quotes. |
Table |
Table |
Table |
| Filter Column |
Name of Table
Column |
| Filter Value |
Text or numeric |
| Filter By
Time Range |
The Filter By
Time Range function returns a copy of the specified Table, containing only those
rows in which the value in the Date/Time Column falls within the given Time
Range. Date/Time Column Name -
Name of a column containing a timestamp. If left blank, the first column is
assumed to contain a timestamp.
Time Range Start - (Optional) A
date/time included in the range.
Time Range End - (Optional) A date/time
included in the range. Note: the actual end time used is one second less than
this value, so it is non-inclusive. |
Table |
Table |
Table |
| Date/Time Column Name |
Name of Table
Column |
| Time Range Start |
Text |
| Time Range End |
Text |
| First Table Rows |
The First
Table Rows function returns the first N rows of the given Table or the first N
rows for each unique combination of values in the given Index Column(s), where N
is the specified Number of Rows to return. If you choose to specify one or more
Index Column Names, the values contained in each index column are concatenated
to form a unique index and N rows are returned for each index. |
Table |
Table |
Table |
| Index Column Names |
Name of Table Column(s) |
| Number of Rows |
Numeric |
| Format
Table Columns |
The
Format Table Columns function applies the specified Column Format(s) to
the Table To Format and returns a formatted table. Only the columns included
in Column Format(s) will be formatted. The Column Format must include column
name(s) and the column format(s) separated by :'s. The column name must
be enclosed in single quotes if it contains a space. The column format
can be specified as a Java format specification, or with the following
shorthand: $ for money values, $$ for money values with additional formatting,
or () for non-money values, formatted similar to money. For example, if
Column Format(s) contains 'Units Completed':$, the Units Completed column
in the returned table would be formatted for money. Both positive and negative
formats can be supplied, for example: #,###;(#,###). |
Table
to Format |
Table |
Table |
| Column
Format(s) |
Text ('Units
Completed':$) |
| Group By Time |
The Group By
Time function returns a table containing a summary of all the data in the given
Table, subtotaled or aggregated over time as indicated by the Group Type. The
summary data in the returned table are grouped into time intervals specified as
a number of Date Parts over a Time Range.
Group Type - The type of aggregation to
perform. If left blank the default is "sum". Permitted values are "sum",
"count", "average", "min", or "max".
Date/Time Column Name - Name of a column
containing a timestamp. If left blank, the first column is assumed to contain a
timestamp.
Date Part - The date unit to use. Enter
s, m, h, d, w, M, q or y, for seconds, minutes, hours, days, weeks, months,
quarters or years. If left blank, the Date Part will default to seconds.
Date Parts Per Interval - The size of
each interval in Date Parts.
Number Of Intervals - The number of
intervals to return in the summary table, one row for each interval. If set to
0, the number of intervals is determined from the range of data in the Table, or
the given Time Range.
Time Range Start - (Optional) A
date/time included in the range.
Time Range End - (Optional) A date/time
included in the range. Note: the actual end time used is one second less than
this value, so it is non-inclusive.
Restrict To Time Range - If set to 1,
the resulting summary table includes only those time intervals within the
specified Range.
Use Column Names - Permits the retention
of the original column names. When Use Column Names is set to 1 the original
column names are retained. If Use Column Names is set to 0, generic column names
will be used. This behavior is useful if a display needs to be independent of
the column names in the data.
|
Table |
Table |
Table |
| Group Type |
Text (e.g., sum, count,
average, min, max) |
| Date/Time Column Name |
Table Column |
| Date Part |
Text (e.g., s,
m, h, d, w, M, q, y) |
| Date Parts per
Interval |
Numeric |
| Number of Intervals |
Numeric |
| Time Range Start |
Text |
| Time Range End |
Text |
| Restrict to Time Range |
Numeric |
| Use Column Names |
Numeric |
Group By Time
and Unique Values |
The Group By
Time And Unique Values function returns a table containing a summary of all the
data in the given Table, subtotaled or aggregated over time and index columns as
indicated by the Group Type. The summary data in the returned table are grouped
into time intervals specified as a number of Date Parts over a Time Range, with
a further breakdown by Unique Values in the Index Columns.
Group Type - The type of aggregation to
perform. If left blank the default is "sum". Permitted values are "sum",
"count", "average", "min", or "max".
Date/Time Column Name - Name of a column
containing a timestamp. If left blank, the first column is assumed to contain a
timestamp.
Date Part - The date unit to use. Enter
s, m, h, d, w, M, q or y, for seconds, minutes, hours, days, weeks, months,
quarters or years. If left blank, the Date Part will default to seconds.
Date Parts Per Interval - The size of
each interval in Date Parts.
Number Of Intervals - The number of
intervals to return in the summary table, one row for each interval. If set to
0, the number of intervals is determined from the range of data in the Table, or
the given Time Range.
Time Range Start - (Optional) A
date/time included in the range.
Time Range End - (Optional) A date/time
included in the range. Note: the actual end time used is one second less than
this value, so it is non-inclusive.
Restrict To Time Range - If set to 1,
the resulting summary table includes only those time intervals within the
specified Range.
Index Column Names - The names of one or
more columns that uniquely identify a row in the table. If left blank, the
default is to aggregate only by time interval. The values contained in each
index column are concatenated to form a unique index used to organize the
resulting summary data.
Value List - A table column containing a
set of values which will be included in the set of values for the first Index
Column. This is useful if you want the summary table to include values that may
or may not be in the Table data.
Restrict To Value List - If set to 1,
the returned table contains only rows that include the values of the specified
Value List.
Use Column Names - Permits the retention
of the original column names. When Use Column Names is set to 1 the original
column names are retained. If Use Column Names is set to 0, generic column names
will be used. This behavior is useful if a display needs to be independent of
the column names in the data. |
Table |
Table |
Table |
| Group Type |
Text (e.g., sum, count,
average, min, max) |
| Date/Time Column Name |
Table Column |
| Date Part |
Text (e.g., s,
m, h, d, w, M, q, y) |
| Date Parts per
Interval |
Numeric |
| Number of Intervals |
Numeric |
| Time Range Start |
Text |
| Time Range End |
Text |
| Restrict to Time Range |
Numeric |
| Index Column Names |
Name of Table Column(s) |
| Value List |
Name of Table Column |
| Restrict to Value List |
Numeric |
| Use Column Names |
Numeric |
| Group by Unique Values |
The Group By
Unique Values function returns a table containing a summary of all the data in
the given Table, subtotaled or aggregated as indicated by the Group Type. The
summary data in the returned table are broken down by all combinations of unique
values found in the specified Index Columns.
Group Type - The type of aggregation to
perform. If left blank the default is "sum". Permitted values are "sum",
"count", "average", "min", or "max".
Index Column Names - The names of one or
more columns that uniquely identify a row in the table. If left blank, the
default is to use the first column in the Table as the index column. The values
contained in each index column are concatenated to form a unique index used to
organize the resulting summary data.
Value List - A table column containing a
set of values which will be included in the set of values for the first Index
Column. This is useful if you want the summary table to include values that may
or may not be in the Table data.
Restrict To Value List - If set to 1,
the returned table contains only rows that include the values of the specified
Value List.
Use Column Names - Permits the retention
of the original column names. When Use Column Names is set to 1 the original
column names are retained. If Use Column Names is set to 0, generic column names
will be used. This behavior is useful if a display needs to be independent of
the column names in the data.
Restrict To Data Combinations - If set to 1, the returned table is restricted to
only those combinations of values found in the specified Index Columns that
occur in the data. If set to 0, the returned table contains all possible
combinations of unique values found in the specified Index Columns. |
Table |
Table |
Table |
| Group Type |
Text (e.g., sum, count,
average, min, max) |
| Index Column Names |
Name of Table
Column(s) |
| Value List |
Name of Table
Column |
| Restrict to Value List |
Numeric |
| Use Column Names |
Numeric |
| Restrict To Data
Combinations |
Numeric |
| Join |
The
Join function performs an inner join of the Left Table and the Right Table
on the columns specified in the Left Column Name and the Right Column Name
fields. The joined table will contain all columns from the Left Table followed
by all columns from the Right Table, and will contain all rows where the
value in the Left Column exactly matches the value in the Right Column.
If a Column Name is left blank, the row name, up to the first : if it contains
a :, will be used instead of a column value. |
Left Table |
Table |
Table |
| Right Table |
Table |
| Left Column Name |
Name of Table
Column, or leave blank to use row name |
| Right Column Name |
Name of Table
Column, or leave blank to use row name |
| Last Table Rows |
The Last Table
Rows function returns the last N rows of the given Table or the last N rows for
each unique combination of values in the given Index Column(s), where N is the
specified Number of Rows to return. If you choose to specify one or more Index
Column Names, the values contained in each index column are concatenated to form
a unique index and N rows are returned for each index. |
Table |
Table |
Table |
| Index Column Names |
Name of Table Column(s) |
| Number of Rows |
Numeric |
|
Max All Rows or Columns |
The
Max All Rows or Columns function finds the maximum value within each column or row of the specified
Table.
All numerical columns will be included in the
calculation.
Result Column - Controls whether to return a column or a row of result values.
To get a column of result values, one value for each row, set Return Column to
1. To get a row of result values, one value for each column, set Return
Column to 0.
Result Label - Used to specify a label for the result row or column. To have the
Result Label placed in a particular column (when Return Column is 0),
specify a column name in Result Label Column. |
Table |
Table |
Table |
| Return Column |
Numeric |
| Result Label |
Text for Column
or Row Label. Default is Maximum. |
| Result Label
Column |
Name of Table
Column |
| Max Columns |
The Max
Columns function finds the maximum value of specific columns within the
specified Table.
NOTE: If a specified column is not found, the function attempts to convert the
string argument to a numeric value. If this succeeds, the value is used as a
constant in each row-wise operation. |
Table |
Table |
Table |
| First Column Name or
Numeric Value |
Text or Numeric |
| Second Column Name or
Numeric Value |
Text or Numeric |
| Result Column Name |
Name of Table Column |
|
Min All Rows or Columns |
The
Min All Rows or Columns function finds the minimum value within each column or row of the specified
Table.
All numerical columns will be included in the
calculation.
Result Column - Controls whether to return a column or a row of result values.
To get a column of result values, one value for each row, set Return Column to
1. To get a row of result values, one value for each column, set Return
Column to 0.
Result Label - Used to specify a label for the result row or column. To have the
Result Label placed in a particular column (when Return Column is 0),
specify a column name in Result Label Column. |
Table |
Table Column |
Table |
| Return Column |
Numeric |
| Result Label |
Text for Column
or Row Label. Default is Minimum. |
| Result Label
Column |
Name of Table
Column |
| Min Columns |
The Min
Columns function finds the minimum value of specific columns within the
specified Table.
NOTE: If a specified column is not found, the
function attempts to convert the string argument to a numeric value. If this
succeeds, the value is used as a constant in each row-wise operation. |
Table |
Table |
Table |
| First Column Name or
Numeric Value |
Text or Numeric |
| Second Column Name or
Numeric Value |
Text or Numeric |
| Result Column Name |
Name of Table Column |
| Modulo Columns |
The Modulo
Columns function divides the specified columns and returns the remainder in a new table column.
NOTE: If a specified column is not found, the
function attempts to convert the string argument to a numeric value. If this
succeeds, the value is used as a constant in each row-wise operation. |
Table |
Table |
Table |
| First Column Name or
Numeric Value |
Text or Numeric |
| Second Column Name or
Numeric Value |
Text or Numeric |
| Result Column Name |
Name of Table Column |
| Multiply
Columns |
The Multiply
Columns function multiplies the specified columns.
NOTE: If a specified column is not found, the
function attempts to convert the string argument to a numeric value. If this
succeeds, the value is used as a constant in each row-wise operation. |
Table |
Table |
Table |
| First Column Name or
Numeric Value |
Text or Numeric |
| Second Column Name or
Numeric Value |
Text or Numeric |
| Result Column Name |
Name of Table Column |
| Percent
Columns |
The Percent
Columns function divides the value of each row in the second column into
corresponding rows in the first column of the specified Table and converts to a
percentage. NOTE: If a specified column is
not found, the function attempts to convert the string argument to a numeric
value. If this succeeds, the value is used as a constant in each row-wise
operation. |
Table |
Table |
Table |
| First Column Name or
Numeric Value |
Text or Numeric |
| Second Column Name or
Numeric Value |
Text or Numeric |
| Result Column Name |
Name of Table Column |
| Pivot
On Unique Values |
The
Pivot On Unique Values function returns a table in which row data from
the given Table has been rotated into columns. The Pivot Name Column contains
values that become new column names in the returned table. The Key Column
is used to group rows containing unique names in the Pivot Name Column
into a single row. The Pivot Value Column contains the data of interest.
All consecutive rows that contain the same value in the Key Column will
have the data in the Pivot Value Column subtotaled into the same row of the
resulting table, in the appropriate column. To include columns in the returned
table for names that are not present in the Pivot Name Column, specify
a table column in the Name List argument which contains all possible names.
If Restrict to Name List is set to 0 or if the Name List is not
specified, all unique values from the Pivot Name Column are included in
the returned table, otherwise only values from the Name List will be included. |
Table |
Table |
Table |
| Key Column |
Name of Table
Column |
| Pivot Name Column |
Name of Table
Column |
| Pivot Value Column |
Name of Table
Column |
| Name List |
Text |
| Restrict To Name
List |
Numeric |
| Reference |
Makes a
reference to the specified Table without copying the contents. This function
returns a table. |
Table |
Table |
Table |
| Select
Column |
Returns
a table containing only the column specified in Select Column Name from
the given Table. This function returns table that contains all rows from
the original table. |
Table |
Table |
Table |
| Select Column
Name |
Name of Table
Column |
| Sort
Table |
The
Sort Table function returns the Table sorted by Sort Column Name. If Sort
Descending is set to 0, the Table is sorted in descending order.
If Sort Descending is set to 1, the Table is sorted in ascending
order.
If your column contains text
it will be sorted alphabetically, unless the text consists entirely of
numbers, in which case it will be sorted numerically. |
Table |
Table |
Table |
| Sort Column Name |
Name of Table
Column |
| Sort Descending |
Numeric |
| Subtotal
By Time |
The
Subtotal By Time function subtotals the values in the specified Table by
the number of Date Part Intervals. The Table must contain a time column
and a number column. This function returns a table containing subtotals
for the Number of Intervals specified or subtotals for all of the data
in the Table if the Number of Intervals is 0. For Date Part, specify
s,
m,
h,
d,
w,
M,
q
or
y for seconds, minutes, hours, days, weeks, months, quarters
or years. If left blank, the Date Part will default to seconds. The optional
Date Format argument controls the format of the time in the returned table,
using the Java SimpleDateFormat class. For example, the format EEE,
hh:mm a will result in a string of the form Wed, 05:32 PM. Use
q,
qqq
or qqqq for short, medium or long versions of quarter notation.
For example, qqq-yyyy will result in a string of the form
Qtr
1-2005.If Date Format
is left blank the Return Type of the first column in the table will be Date,
otherwise it will be String. If Use Column
Names is set to 1, then original column names will be retained. If set to 0,
columns will be given generic names (e.g.: Subtotal1, Total1, etc.). Generic
column names are useful when the data attachment for the Table argument uses a
substitution that will cause the column names to change when the substitution
changes. |
Table |
Table (must contain
time column and numeric column) |
Table |
| Interval |
Numeric |
| Number of Intervals |
Numeric |
| Date Part |
Text (e.g., s,
m, h, d, w, M, q, y) |
| Date Format |
Text (e.g., MMMM dd, yyyy
hh:mm:ss a) |
| Use Column Names |
Numeric |
| Subtotal
By Unique Values |
The Subtotal
By Unique Values function returns a table listing all of the unique values
found in the first column of the Table Columns argument, along with the
sum of the values in the corresponding fields of the remaining Table Columns.
To include rows in the returned
table for values that are not always present in the Table Columns, specify
a table column in the Value List argument which contains all possible values.
If Restrict to Value List is set to 0 or if the Value List is not specified,
all unique values from the Table Columns will be included in the returned
table. Otherwise, only values from the Value List will be included. This
function returns a table.
If Use Column Names is set to 1, then
original column names will be retained. If set to 0, columns will be given
generic names (e.g.: Subtotal1, Total1, etc.). Generic column names are useful
when the data attachment for the Table argument uses a substitution that will
cause the column names to change when the substitution changes. |
Table Columns |
Table Columns
(enter two table columns) |
Table |
| Value List |
Table Column |
| Restrict to Value List |
Numeric |
| Use Column Names |
Numeric |
| Subtract
Columns |
The Subtract
Columns function subtracts the value of each row in the second column from the
corresponding rows in the first column of the specified Table.
NOTE: If a specified column is not found, the
function attempts to convert the string argument to a numeric value. If this
succeeds, the value is used as a constant in each row-wise operation. |
Table |
Table |
Table |
| First Column Name or
Numeric Value |
Text or Numeric |
| Second Column Name or
Numeric Value |
Text or Numeric |
| Result Column Name |
Name of Table Column |
|
