Value At (version 22)

Knowledge Base / Version 22 / Client Tools / Excel Add-in

updated 2 yrs ago

The 'Value At' function finds TVQs that meet a set of criteria for a set of tags. If found there will be one TVQ (timestamp, value, quality) for each tag.

Input Range

A reference to a range of cells containing a list of tag names typically generated by the Tag List function. This range is automatically set to the cells that are pre-selected when the button for this function is selected. The range can be adjusted by editing the range or selecting a new range of cells then clicking the 'Set' button. Adjusting the input range will automatically update the output location.

Start Date

The 'Start Date' can be an absolute date (11/21/2022), a relative time (NOW-5h), or a cell reference ('Sheet6'!$G$12). The absolute date can be typed in or the Calendar control may be used by pressing the down arrow at the end of the text box. If a relative time is specified, then the 'Start Time' does not need to be used. To set a cell reference, select the desired cell and press the 'Set' button.

Start Time

Used in conjunction with an absolute start date, 'Start Time' can be an absolute time (10:15:00 AM) or a cell reference ('Sheet6'!$G$12). To set a cell reference, press the 'Set' button with the desired cell selected.

Seek Direction

Select between Forward and Backward. The function will look for the first TVQ it encounters from the 'Start Date/Time' that matches the 'Expression' criteria.

Expression

Choose between 11 expressions:

First Value - return first TVQ encountered.
value > {number} - value is greater than a specified number.
value < {number} - value is less than a specified number.
value >= {number} - value is greater than or equal to a specified number.
value <= {number} - value is less than or equal to a specified number.
value = {number} - value is equal to a specified number.
value <> {number} - value is not equal to a specified number.
quality = {number} - quality is equal to a specified number.
Quality is Good - return first TVQ with a good quality.
Quality is Bad - return first TVQ with a bad quality.
Quality is NoData - return first TVQ with a "NoData" quality.

The expression uses two key words: value and quality. A valid expression could be (value > 55.2) or (value < 10.2) or (value = 25). Using the quality keyword, an expression might be (quality = 0xC0) or (quality <> 0xC0). The expression will accept both decimal and hexadecimal number (preceded by 0x). The expression will also accept (value > 33.3 && value < 44.4) to find a value that is in a certain range. An expression can also mix both quality and value keywords like (value > 55.2 && quality = 0x2C0). The comparative operators are >, <, >=, <=, =, <>. The && is the AND operator and || is the OR operator. The expression can only be applied to a single TVQ.

Show Headers

This option will display a heading for each 'Display Field' included in the output.

Display Fields

If the 'Display Field' name is checked, that field will be included in the output results. The order of the display field names determines the order in which they are outputted. To rearrange the order, click a field and then press the up/down buttons along the right side. You can also drag-and-drop fields to rearrange them.

TimeStamp as Text - timestamps are returned as as formatted string down to one second resolution (10/4/2018 12:00:00 AM).
TimeStamp as Number - timestamps are returned as an Excel DateTime number (needs to have Excel formatting applied to make it readable).
Value if Good - The TVQ value if the quality is good, otherwise blank.
Value - The TVQ value, regardless of quality.
Data Type - The TVQ value data type.
Decimal Quality - The TVQ quality in decimal format (i.e. 192, 0, 64, 32768).
Hex Quality - The TVQ quality in hexadecimal format (i.e. C0, 0, 40, 8000).
Readable Quality - The TVQ quality - in a readable format - of the different bit meanings (i.e. Good, Bad, Uncertain, No Data).

Output Vertically

This option transposes the output columns and rows.

Output Location

This is the location where the output will be written to. It is the cell in the upper left corner of the result. The number of cells needed for the result will depend on the input parameters. The number of columns and rows returned is dependent on the number of tags and display fields. The default output location cell is set to the cell just below the left most cell of the input range (if the input range is horizontal) or just to the right of the top most cell of the input range (if the input range is vertical.)

When using input parameters referencing other cells, if the input cell changes it will automatically update the resulting data.

The Input Pane can be closed at any time by clicking on the X in the upper right corner. To re-open the Pane, click on the Canary Ribbon "Value At" button. If the cell containing an existing Canary function has the focus and the user selects the corresponding function button from the Canary Ribbon, the input controls in the Pane will be set according to the parameters of the Canary function.