Tables

The Table tab (shown in Figure 1) provides a textual representation of your data. In the simplest case where you open an instance of the Charting Tool with a single time series, the Table tab will contain two columns, one with time-stamps and the other with observations corresponding with that time-stamp. The date format is taken from your computer's system settings, which can be changed using the System settings.

If you assemble two or more graphs in the Chart tab, the Table tab will contain additional columns, one per graph, with the observations aligned with respect to time.

  • The Date column refers to the start of the water year; and
  • Enabling the Filter matching rows checkbox will filter the table (as per the Search criteria) and remove non-matching entries. Note that this process may be slow depending on table size and host machine.

You can use the Search functionality to view data that falls within a range, for example. The field accepts boolean characters as well.

 

Figure 1. Table tab

The tick box at the bottom right allows you to toggle how the column headings are displayed - in long or short form. For example, if the column contents were "Latest Run > Minimum Flow Requirement > Lake Release > Upstream Flow Volume" the short header would be "Upstream Flow Volume: Latest Run" which provides basic information about the column contents and is easier to read but may not provide all the information required.

 

Note: The date is displayed (and can be modified) as per the short date format set in the host computer's Control Panel (see figure below).


Table Query Language

The Table Query Language (TQL "tickle" [tik-uh l]) is an interpreted language used as part of Results Manager Table tab to highlight / filter columns based on user specified criteria.  Queries are entered by the user into the search bar at the top of the columns and the results are highlighted within the table columns (Figure 2).  An optional filtering check-box can also be selected which will ensure that only rows containing matches are displayed.   Successful queries (meaning those that have resulted in cell matches result in the query string)  are displayed in green, while unsuccessful queries are displayed in red.

Figure 2. Table tab, TQL search example. 
TQL Overview

Supported Features

TQL can be used to search for the following information:

  • Arbitrary strings - these attempt to match the characters typed in to any raw content shown in any of the cells.  e.g., "01/07" will match any year's date "01/07/2001", "01/07/2002"
  • Date Range - only a block of dates can be specified.  e.g., "01/07/2001 > && < 01/07/2002"
  • Any Field Value - numeric logic applied to all cells e.g., "> 10 && < 20"
  • Specific Column Value - like the any field match but specific to particular columns e.g., "$1 > 10 && $2 < 20"
  • Cross Column Comparisons - look for relationships between columns e.g., "$1 > $2"

Language Structure

There are two main search types:

  1. String - these searches attempt to find the entered substring in any table cell.  No logical expressions can apply to these searches as they have no contextual information.
  2. Expression - these searches determine the actual value of the cell in the appropriate format and allow for logical relationships

Which are based around four main types of cell information

  1. Dates (Expression) - e.g., "01/07/2001"
  2. Numerics (Expression) - e.g., "155.51"
  3. Column Specifications (Expression) - e.g., "$1"
  4. Arbitrary Strings (String) - e.g.,  "12/1", "12."

When a search is entered into the table, the parser will attempt to always match an Expression search.  In cases where this fails it will revert back to a String search.  Both String and Expression searches can be combined but no equality can be used to qualify the String portion

For example, if the user entered  "12/1" it would be a String search as there is not enough text entered to form a full date. This would result in it scanning through all cells and matching any in the Date column which contain the exact substring.   Entries such as: "12/10/2005" and "05/12/1001" would be highlighted.  However, if a full valid date is entered such as "12/10/2005" then this would be an Expression search and only the "12/10/2005" would be matched.  The downside of String searches is that they no not allow for any logic expressions as there is no context to the match.  So the user can't look for later dates based on the "> 12/1String search, where-as the Expression search "> 12/10/2005" will.

Equality operations

The equality operations supported are shown in Table 1. It can be noted that the not equal and equal operators have two versions which can be used.  This is for flexibility and both versions operate in an identical manner.

Table 1. Table Query Language, Comparison operators

Operator

Meaning

>

greater than

<

less than

>=

greater than or equal

<=

less than or equal

= or ==

equal

!= or !

not equal

Logical / Boolean operations

The following boolean / logical operations are supported:

  • "&" or "&&" - logical AND
  • "|" or "||" - logical OR

As with the equality operators, boolean AND and OR have two versions which can be entered but each operate identically.  They evaluate each side of the expression and then combine the results as specified using boolean logic.

  • AND is true when both sides of expression are true
  • OR is true when either or both sides of the expression are true

TQL does not currently support any other logical connectives such as XOR.

Column Notation

When wanting to specify a particular column, the user can enter the column number (starting at 0) preceded by the dollar symbol ("$").  So to specify the Date column the user would enter "$0" whereas to specify the first value column they would enter "$1".  At evaluation time the parser will replace the column specified with the particular cell value and then evaluate.  This is useful for looking for relationships amongst two different time-series.

When no column is specified it is assumed any column can match

Figure 3. TQL, column notation.

Column Specifiers

Date Format

TQL can use dates as a value for evaluation.  It does this by converting the Date value into the machine's current Date culture.  This means that user should be aware of if they are using "dd/mm/yyyy" or "mm/dd/yyyy" format specifiers as part of their general operating system set-up.  Dates can only be used to compare and perform logical comparisons to other Dates within the first column ("$0")

Determining date culture

A rule of thumb is to copy the format used in the Date column as this will already be in the culture specific format.

TQL Examples

The following examples in Table 2 show typical usage, all date usages assume a culture format "dd/mm/yyyy".  For clarity, queries that are interpreted as a String search are indicated with an (S) while Expression searches are marked with an (E).

Table 2. TQL Examples
Purpose TQLTypeComments
Find a partial date match01/07/(S)Finds the first of July for each year
Find a specific date$0 = 01/07/2005(E)Finds the row with the exact date 1st July 2005
Find all dates after a specific date$0 > 01/07/2005(E)Finds all dates later than the 1st July 2005
Find a date range$0 > 01/07/2005 & $0 < 01/08/2005(E)Finds all the dates later than the 1st July 2005 but earlier than the 1st August 2005
Find a partial value match.2(S)Finds all entries whose fractional component starts with .2
Exact value match= 100.2(E)Finds all entries with the exact value of 100.2
Exact value column match$1 = 100.2(E)Finds all entries in column 1 (the first value column) with the exact value 100.2
Find a value range> 100 & < 200(E)Finds all entries whose value is between 100 and 200
Find values not equal!= 100(E)Finds all entries whose value is not 100
Specific column value match$1 > 100 & $2 < 200(E)Finds all entries in column 1 greater than 100 while at same time there are entries in column 2 with values less than 200
Column Relationships$1 = $2(E)Finds all entries where the value of column 1 is the same as the value in column 2
Expression Combinations$0 > 01/07/2005 & $1 < 300(E)Finds all entries where the date is later than the 1st July 2005 and the value of column 1 is less than 300
String and Expression Combinations07/2005 & $1 = 100(S) (E)Finds all entries in July 2005 where the value of 100 is in column 1

Appendix - Language Definition

TQL is a context-free grammar which ignores white-space.  The Extended Backus-Naur Form metasyntax for TQL is as follows:

   Statement := String | Expression, Logical Expression };
  Expression := [Value] Logical Value;
       Value := Number | Date | Column;
      String := Digit | Point | Slash { Digit | Point | Slash };
     Logical := "&&" | "&" | "|" | "||";
    Equality := "<" | "<=" | ">" | ">=" | "!=" | "!" | "==" | "=";
      Column := "$"Natural;
        Date := Digit [, Digit]SlashDigit [, Digit]SlashDigitDigitDigitDigit;
     Natural := Digit {Digit};
     Integer := ["-"]Natural;
       Point := ".";
       Slash := "/";
       Digit := "0" | "1" | "2" | "3" | "4" | "5" | "6" | "7" | "8" | "9";
      Number := Integer[PointDigit {Digit}];