GROUPBYTABLE element - Intergraph Smart 3D - Help - Hexagon

Defines a new table that is derived from the main TABLE element. Multiple GROUPBYTABLE elements are allowed under a TABLE element. Typically, the GROUPBYTABLE element defines a table where items are grouped by a set of columns that share the same values and contains aggregated values such as SUM, MIN, MAX and COUNT. For example, to create a table that contains a list of component types, such as valve, flange, and so forth, together with a count of the number of occurrences, you use the GROUPBYTABLE element. An example is shown below:

The GROUPBYTABLE element contains the following attributes:

• Name specifies the name of the derived table, such as Name="T1". This attribute is mandatory.

• Filter defines which rows from the Table element are included in the derived table. A filter expression refers to one or more column names, as defined in the COLUMN element. Those rows for which Filter is set to True are included in the data output to the derived table.

  The Filter attribute is a string that can contain any of the following:

  • Column names

  • Boolean operators AND, OR, or NOT

  • Brackets to group clauses

  • Comparison operators <>< > <= >= <> = LIKE

  • Mathematical operators +, -, ?, *.

  • Wildcards * or %

  • Functions CONVERT, LEN, ISNULL, IF, TRIM, or SUBSTRING

• Filter is an optional attribute. However, if you do not define a Filter attribute, all rows from the table are included in the derived table.

• For a full description of syntax, see Syntax of Expression Statements and FILTER Expressions.

• If the Filter attribute contains strings, use the single quote character ( ' ) for the string constant and the double quote character ( " ) for the Filter expression. Examples are shown below.

Filter="SKEY="TML0"
Filter="C105 LIKE „product*" AND C106 < C107"

• Fields defines which columns and aggregated columns are included in the derived table. The Fields attribute contains a string with a field list that references one or more columns from the master table. The basic syntax is [aggregate function] Column_name [alias], where aggregate function can be COUNT, SUM, MAX, or MIN. The optional alias is used to specify a new name for the column. This attribute is mandatory.

  Fields='C101, SKEY, SUM(C105) TOT"

• GroupBy defines which column or columns are used to group rows in the derived table. Where the columns have the same values, the rows are aggregated. For example, GroupBy="C101" groups all rows with the same value in column C101:

  TABLE
  C101
  1
  2
  2
  3
  1

• A GROUPBYTABLE element must contain at least one VIEW element. These are identical to the VIEW elements within the master TABLE element and work the same way.

• You can have multiple GROUPBYTABLE elements under a TABLE element.