X query (Excel): Difference between revisions

From Tygron Support wiki
Jump to navigation Jump to search
(Created page with "framed|right|Example of what will happen with an X query with 3 neighborhoods. In some cases, especially when developing projects which are prone to change...")
 
No edit summary
 
(57 intermediate revisions by 5 users not shown)
Line 1: Line 1:
[[File:Xquery.jpg|framed|right|Example of what will happen with an X query with 3 neighborhoods.]]
[[File:Xquery.jpg|thumb|right|x150px|Example of what will happen with an X query with 3 neighborhoods.]]
In some cases, especially when developing projects which are prone to change, or when creating [[project templates]], it may be desirable to add queries to your excel sheet which retrieve all of a certain type of thing. For example, each project can have a different amount of [[neighborhoods]]. In these cases you can't create queries for all the items you wish to retrieve. Instead, you can use X queries to instruct the {{software}} to dynamically create the queries for you. For any given query with uses an ID, you can replace the ID with <code>X</code>, with the result being a query such as <code>SELECT_UNITS_WHERE_NEIGHBORHOOD_IS_1</code> turning into <code>SELECT_UNITS_WHERE_NEIGHBORHOOD_IS_X</code>. Only one <code>X</code> term may exist in a query, but you may add multiple X queries to an Excel file.
For use-cases in which an [[Excel]] is used to aggregate data, for example to compute specific scores per [[Neighborhood]] for display in an [[Indicator]], it is not desirable to create individual [[TQL]] queries for each piece of data that needs to be retrieved. Rather than stating each individual [[Neighborhood]] for which to retrieve data, an X query can be used to indicate that, across multiple rows, the data of all [[Neighborhood]]s should be retrieved, regardless of how many or how few that may be.


When an Excel file with one or more X queries is uploaded, the {{software}} prepares it for calculation by transforming the single X query into a column of queries which refer to specific IDs. When multiple X queries have their <code>X</code> refer to the same type of element from the project, the amount and order in which the IDs are filled in are consistent. The created queries are placed in the same column as the original X query, starting in the cell directly below the X query and continuing downward. The X queries are then removed. In other words, when using an X query, the first cell in that column with the intended query will be the cell under the X query. Each cell which now has a query will be properly filled in by the {{software}}. Note that not every cell in the column is given a query. If there are more cells with values than are overwritten by queries, those excess cells retain their value. From this point on, the file is used as normal.
Where a normal query would be of the following form:
 
<code>SELECT_LANDSIZE_WHERE_NEIGHBORHOOD_IS_1</code>
 
Multiple such queries would be required to retrieve the sizes of all [[Neighborhood]]s. Instead, it is possible to create an X query of the following form:
 
<code>SELECT_LANDSIZE_WHERE_NEIGHBORHOOD_IS_X</code>
 
The {{software}} will interpret this as an instruction to create additional, specific queries in the excel, one for each [[Neighborhood]] in the [[Project]].
 
An X query can still consist of multiple clauses, including clauses referring to specific [[Item]]s.
 
<code>SELECT_LANDSIZE_WHERE_NEIGHBORHOOD_IS_X_AND_STAKEHOLDER_IS_0_AND_TERRAIN_WITH_ATTRIBUTE_IS_WATER</code>
 
X queries can be used to automatically retrieve:
* [[Address]]es
* [[Area]]s
* [[Building]]s
* [[Indicator]]s
* [[Neighborhood]]s
* [[Net Cluster]]s
* [[Net Line]]s
* [[Net Load]]s
* [[Overlay]]s
* [[Panel]]s
* [[Zone]]s
* [[Attribute_array|Indexes in Attribute Array]]s
* [[Timeframe|Timeframes of Grid]]s
{{clear}}
 
==How X queries are resolved==
When parsing an X query, the {{software}} finds all X queries in the Excel, and (re)generates the resulting queries. For any clause with an X, it finds all [[Item]]s specified by the clause, and creates a query for each [[Item]] found in this way. The specific queries are generated in the cells below the cell where the X query was placed.
 
The order of items can be assumed to be in order of [[ID]], and as such consistent for all X queries which have an X for the same clause.
 
When an Excel file is uploaded or has its X queries otherwise reset, the {{software}} finds all X queries in the Excel, and (re)generates the resulting queries. For any clause with an X, it finds all [[Item]]s specified by the clause, and creates a query for each [[Item]] found in this way. The specific queries are generated in the cells below the cell where the X query was placed.
 
It is important to note that:
* The originally uploaded [[Excel]] file remains unaltered. This means when it is downloaded, either normally or as a debug file, the generated queries will not be visible.
* The queries are ''only'' (re)generated when an Excel file is uploaded/updated, or [[Calculation_panel#Update_Indicators_and_Reset_Excel_X-Queries|Excel X queries are explicitly reset]]. This means that if additional [[Item]]s are added to a [[Project]] which would match a clause with an X, queries for those [[Item]]s are not automatically (re)generated.
* In a [[Project]] in which [[Item]]s have been deleted, the [[ID]] of that [[Item]] would no longer make a valid query, and is therefor skipped while generating specific queries. IF this happens in a sequence, e.g. 4 [[Neighborhood]]s with [[ID]]s 0 through 3, of which those with [[ID]] 1 and 2 have been deleted, the first generated query would be for the [[Neighborhood]] with [[ID]] 0, followed directly in the next row by a query for the [[Neighborhood]] with [[ID]] 3.
* If a query generated from an X query resolves to the exact same query which already exists elsewhere in the [[Excel]], even if that query is also generated, only one of those queries will actually function. The cell where the other would have led to an inserted value will remain empty.
* Although X queries tend to serve as a header for an entire column of values; if there are already values in the column, and there are not sufficient queries generated to overwrite them all, then the cells which have a value but do not have a generated query will retain their cell value. They will not be emptied.
* A maximum of 64.000 queries can be generated in the vertical direction from a single X query.
 
===Resetting X queries===
X queries are not continuously interpreted for the generation of the appropriate specific queries. The queries are ''only'' (re)generated when an Excel file is uploaded/updated, or [[Calculation_panel#Update_Indicators_and_Reset_Excel_X-Queries|Excel X queries are explicitly reset]].


The original Excel file is saved by the {{software}}. When a change occurs which would affect how many and which queries should be generated from an X query, it's possible the Excel file needs to be re-prepared for use in the {{software}}. It is possible to trigger the {{software}} to do this, by using the "Reset X Queries" option in the editor.
{{Editor location|reset x}}
{{Editor location|reset x}}


When the original file is downloaded, it will be the file with the X queries, before the exact queries were generated from them. When the file with the current values is downloaded, it will contain the generated queries, and not the X queries themselves. It is recommended that you don't reupload the file with current values, because it is no longer generically applicable.
This means that if additional [[Item]]s are added to a [[Project]] which would match a clause with an X, queries for those [[Item]]s are not automatically (re)generated.
 
In addition, that also means that during a [[Session]] or [[Test_Run|Test run]], the queries which exist are only those which were already generated at the start of a session. An X clause for [[Building]]s will not automatically include [[Building]]s which were constructed during a [[Session]] or [[Test_Run|Test run]].
 
==Variations==
Multiple variations of X queries are possible, which extend the functionality of X queries to be either broader or more restrictive. Variations can also be combined in single queries.
 
All variations, including those which do not feature an actual X in their syntax, fall under the term "X query".
 
===XY queries===
[[File:XYquery.jpg|thumb|right|x150px|The orange cell is named with an XY query for Neighborhoods and Stakeholders. The resulting matrix shows how much of which Neighborhood is owned by which Stakeholder, in a Project with 3 of each.]]
XY queries allow queries to be generated in 2 dimensions in the [[Excel]], creating a matrix of queries to retrieve values with. The queries will be generated in the downward direction with the X being replaced by applicable [[ID]]'s for that clause, and to the right with the Y being replaced by applicable [[ID]]s for that clause. This means that on each row, queries exist in which the X has been replaced by the same values but the Y values differ, and that in each column the Y has been replaced by the same values by the X values differ.
 
As with X queries without a Y, the first generated query will be generated for the cell directly below the cell containing the XY query. The first query is ''not'' also shifted to the right by the presence of a Y.
 
A maximum of 16.000 queries can be generated in the horizontal direction from a single XY query.
 
An example of a XY query can be:
 
<code>SELECT_LOTSIZE_WHERE_NEIGHBORHOOD_IS_X_AND_STAKEHOLDER_IS_Y</code>
 
{{clear}}
 
====Y queries====
Using a Y in a query can be done even when there is no clause using X in the same query. This means that the query will only be expanded to the right and not downward.
 
When using a query with only a Y, the first generated query is ''not'' shifted downward, and ''not'' shifted to the right. The first generated query is placed in the same cell as the Y query itself.
 
As with an XY query, a maximum of 16.000 queries can be generated in the horizontal direction from a single Y query.
{{clear}}
 
===Multi X queries===
[[File:XXquery.jpg|thumb|right|x150px|The generated queries go through all Areas for one Neighborhood first, then for the next Neighborhood, until all combinations have been made. The output is the amount of overlap between each combination of specific Neighborhood and specific Area.]]
It is possible to have multiple clauses with an X (multiple queries with a Y) in the same query. In these situations, both X's are expanded in the same direction. Both (or all) clauses resolves to their first applicable ID. For the next generated query, only one of the generated queries resolves to the next ID, while the other(s) remain set to their first ID. This continues until the all applicable IDs of that clause have been used. Then, the other clause's ID is advanced by one, and the first clause starts at its first ID again, progressing until all its applicable IDs are exhausted.
 
For virtually all use-cases, it is more convenient to use an XY query than a multi X query, as it makes it easier to parse exactly which values have which meaning, and to structure the data for further calculations.
{{clear}}
 
===XA queries===
[[File:XAquery.jpg|thumb|right|x150px|The orange cell is named with an XA query for Areas with SEWER_STORAGE. The first columns retrieve all Area names and their SEWER_STORAGE values (if any). The XA query returns only the names of the Areas with that Attribute.]]
The X syntax can be expanded to be more selective on which [[Item]]s to generate queries for, using the XA syntax. For example, rather than generating queries for all [[Area]]s in a [[Project]], which may all serve a wildly different purpose, it is possible to retrieve only the [[Area]]s with a specific [[Attribute]].
 
A query which would retrieve the names only of [[Area]]s which have a [[Sewer storage (Water Overlay)|SEWER_STOARGE]] [[Attribute]], marking them as [[Sewer area (Water Overlay)|Sewer Area]]s for use with the [[Water Overlay]], could be constructed as follows:
 
<code>SELECT_NAME_WHERE_AREA_IS_XA_SEWER_STORAGE</code>
 
This will generate only queries for [[Area]]s which have an [[Attribute]] SEWER_STORAGE, in which "XA_SEWER_STORAGE" is in its entirety replaced by the relevant [[Area]]'s [[ID]]. [[ID]]s of [[Area]]s which do not have the specified [[Attribute]], or of which the [[Attribute]]'s value is 0, are skipped in the same way that the [[ID]]s of deleted [[Item]]s are skipped.
 
====SORTed queries====
Expanding on the XA syntax, it is also possible to sort the resulting [[Area]]s by the value of a specific attribute. This attribute may or may not be the same attribute as indicated in the first part of the XA syntax. For example:
 
<code>SELECT_NAME_WHERE_AREA_IS_XA_SEWER_STORAGE_SORT_SEWER_PUMP_SPEED</code>
 
This will generate only queries with a SEWER_STORAGE attribute, as before, but generate them not in the order of the [[Area]]'s respective [[ID]]s, but in the order of the value of their SEWER_PUMP_SPEED attribute.
 
If an [[Item]] does have the XA [[Attribute]], but is missing the SORT [[Attribute]], then it is treated as if its [[Attribute]] on which to sort had a value of 0.
 
=====SORTed RELATION queries=====
Some [[Item]]s may also have a relation to other [[Item]]s. For example, an [[Area]] generated using the [[Border Area Generator]] will have relations with the two [[Area]]s it was generated between. It is possible to sort the generated queries of an XA query not on [[Attribute]]s of the [[Item]] itself, but on the [[Attribute]] of the [[Item]], it has a specific relation with. For example, in a [[Project]] where borders have been generated between all [[Area]]s with a SEWER_STORAGE [[Attribute]]:
 
<code>SELECT_NAME_WHERE_AREA_IS_XA_BORDER_SORT_SEWER_STORAGE_RELATION_BORDER_A</code>
 
This will retrieve all [[Area]]s with a BORDER [[Attribute]] (which is the default [[Attribute]] added to generated border [[Area]]s). For each border [[Area]], it will look at the BORDER_A relation (which indicates another [[Area]]), and from that [[Area]] obtain the value of SEWER_STORAGE. The [[Area]]s with a BORDER [[Attribute]] are then sorted according to those retrieved values. In that order, queries are generated retrieving the name of the [[Area]]s which have a BORDER [[Attribute]].
 
This is one of the most complex syntaxes but is also rarely applicable.
{{clear}}
 
===Index and timeframe===
[[File:XTquery.jpg|thumb|right|x150px|The orange cell is named with an XY query for calculated volumes of water per Neighborhood and per timeframe.]]
For most cases, the X applies to a replacement value for an [[Item]]'s [[ID]]. Two clauses form an exception to this: the INDEX clause and the TIMEFRAME clause.
 
The following queries are allowed, and will be expanded according to the same rules as other X queries:
 
<code>SELECT_ATTRIBUTE_WHERE_NAME_IS_BREACH_HEIGHT_AND_AREA_IS_0_AND_INDEX_IS_X</code>
 
This will result in multiple queries, each retrieving a sequential value from the array of values of the BREACH_HEIGHT [[Attribute]], of the [[Area]] with [[ID]] 0.
 
<code>SELECT_GRIDAVG_WHERE_GRID_IS_3_AND_TIMEFRAME_IS_X</code>
 
This will result in multiple queries, each retrieving the average result of a grid calculation for the entire map, for sequential timeframes.
 
This functionality is commonly combined with the ability to use an additional Y clause to retrieve a matrix of values for multiple [[Item]]s.
{{clear}}
 
===XC queries===
The XC syntax can be used to generate queries for all coverable [[Item]]s which lay within the covering Item. This syntax behaves like an [[ID query (Excel)|ID query]], in that the query makes use of an implicit relation to another feature.
 
It can only be used in an [[Excel]] which is related to a specific Item, such as generated by an applied [[Template Panel]]. The covering [[Item]] is the [[Item]] referenced by the Excel Panel. The coverable [[Item]] is indicated with the XC clause.
 
An XC clause is interpreted to mean "all those [[Item]]s of which the center point lies within the geometry of the [[Item]] this query is related to". For example, in an [[Excel]] which is used in a [[Template Panel]] which has been applied on [[Neighborhood]]s:
 
<code>SELECT_NAME_WHERE_ADDRESS_IS_XC</code>
 
This will expand into a series of queries to obtain the names of all addresses in the [[Neighborhood]].


Please also note that X queries only refer to elements that were already present before the start of a test run. Any change done during a test run (placing of a building, adding of a neighborhood, etc) has NO effect on the X queries.
Coverable [[Item]]s which can be related with XC-queries are:
* Area
* Address
* Building
* Neighborhood
* Zone
* Net Load
* Net Line


===XA Queries===
{{article end
To select specific Items (for example [[Neighborhoods|neighborhoods]] or [[Areas|areas]]) in combination with a X query, use a XA query.
|notes=
XA queries will select only Items with a specific [[Attribute|attribute]]. <br>
* Because X-queries are processed as an explicit operation in the Editor mode, the exact set of [[Item]]s which are retrieved as a result of an X query will not change after a [[Session|non-editing Session]] or [[Test Run]] is started. This means that, for example, [[Building]]s added as an [[Action]] during a [[Session|Single, Multi or Test-run Session]] will not be retrieved.
Example: SELECT_ID_WHERE_AREA_IS_X will show all [[Areas|areas]], using SELECT_ID_WHERE_AREA_IS_XA_PEIL will only show the areas with the [[Attribute|attribute]] PEIL (where the value of PEIL is not 0).
|seealso=
* [[Excel]]
* [[TQL]]
}}

Latest revision as of 09:59, 1 March 2023

Example of what will happen with an X query with 3 neighborhoods.

For use-cases in which an Excel is used to aggregate data, for example to compute specific scores per Neighborhood for display in an Indicator, it is not desirable to create individual TQL queries for each piece of data that needs to be retrieved. Rather than stating each individual Neighborhood for which to retrieve data, an X query can be used to indicate that, across multiple rows, the data of all Neighborhoods should be retrieved, regardless of how many or how few that may be.

Where a normal query would be of the following form:

SELECT_LANDSIZE_WHERE_NEIGHBORHOOD_IS_1

Multiple such queries would be required to retrieve the sizes of all Neighborhoods. Instead, it is possible to create an X query of the following form:

SELECT_LANDSIZE_WHERE_NEIGHBORHOOD_IS_X

The Tygron Platform will interpret this as an instruction to create additional, specific queries in the excel, one for each Neighborhood in the Project.

An X query can still consist of multiple clauses, including clauses referring to specific Items.

SELECT_LANDSIZE_WHERE_NEIGHBORHOOD_IS_X_AND_STAKEHOLDER_IS_0_AND_TERRAIN_WITH_ATTRIBUTE_IS_WATER

X queries can be used to automatically retrieve:

How X queries are resolved

When parsing an X query, the Tygron Platform finds all X queries in the Excel, and (re)generates the resulting queries. For any clause with an X, it finds all Items specified by the clause, and creates a query for each Item found in this way. The specific queries are generated in the cells below the cell where the X query was placed.

The order of items can be assumed to be in order of ID, and as such consistent for all X queries which have an X for the same clause.

When an Excel file is uploaded or has its X queries otherwise reset, the Tygron Platform finds all X queries in the Excel, and (re)generates the resulting queries. For any clause with an X, it finds all Items specified by the clause, and creates a query for each Item found in this way. The specific queries are generated in the cells below the cell where the X query was placed.

It is important to note that:

  • The originally uploaded Excel file remains unaltered. This means when it is downloaded, either normally or as a debug file, the generated queries will not be visible.
  • The queries are only (re)generated when an Excel file is uploaded/updated, or Excel X queries are explicitly reset. This means that if additional Items are added to a Project which would match a clause with an X, queries for those Items are not automatically (re)generated.
  • In a Project in which Items have been deleted, the ID of that Item would no longer make a valid query, and is therefor skipped while generating specific queries. IF this happens in a sequence, e.g. 4 Neighborhoods with IDs 0 through 3, of which those with ID 1 and 2 have been deleted, the first generated query would be for the Neighborhood with ID 0, followed directly in the next row by a query for the Neighborhood with ID 3.
  • If a query generated from an X query resolves to the exact same query which already exists elsewhere in the Excel, even if that query is also generated, only one of those queries will actually function. The cell where the other would have led to an inserted value will remain empty.
  • Although X queries tend to serve as a header for an entire column of values; if there are already values in the column, and there are not sufficient queries generated to overwrite them all, then the cells which have a value but do not have a generated query will retain their cell value. They will not be emptied.
  • A maximum of 64.000 queries can be generated in the vertical direction from a single X query.

Resetting X queries

X queries are not continuously interpreted for the generation of the appropriate specific queries. The queries are only (re)generated when an Excel file is uploaded/updated, or Excel X queries are explicitly reset.

Editor → Recalculation icon (Ribbon bar) → Reset X queries (Dropdown)

This means that if additional Items are added to a Project which would match a clause with an X, queries for those Items are not automatically (re)generated.

In addition, that also means that during a Session or Test run, the queries which exist are only those which were already generated at the start of a session. An X clause for Buildings will not automatically include Buildings which were constructed during a Session or Test run.

Variations

Multiple variations of X queries are possible, which extend the functionality of X queries to be either broader or more restrictive. Variations can also be combined in single queries.

All variations, including those which do not feature an actual X in their syntax, fall under the term "X query".

XY queries

The orange cell is named with an XY query for Neighborhoods and Stakeholders. The resulting matrix shows how much of which Neighborhood is owned by which Stakeholder, in a Project with 3 of each.

XY queries allow queries to be generated in 2 dimensions in the Excel, creating a matrix of queries to retrieve values with. The queries will be generated in the downward direction with the X being replaced by applicable ID's for that clause, and to the right with the Y being replaced by applicable IDs for that clause. This means that on each row, queries exist in which the X has been replaced by the same values but the Y values differ, and that in each column the Y has been replaced by the same values by the X values differ.

As with X queries without a Y, the first generated query will be generated for the cell directly below the cell containing the XY query. The first query is not also shifted to the right by the presence of a Y.

A maximum of 16.000 queries can be generated in the horizontal direction from a single XY query.

An example of a XY query can be:

SELECT_LOTSIZE_WHERE_NEIGHBORHOOD_IS_X_AND_STAKEHOLDER_IS_Y

Y queries

Using a Y in a query can be done even when there is no clause using X in the same query. This means that the query will only be expanded to the right and not downward.

When using a query with only a Y, the first generated query is not shifted downward, and not shifted to the right. The first generated query is placed in the same cell as the Y query itself.

As with an XY query, a maximum of 16.000 queries can be generated in the horizontal direction from a single Y query.

Multi X queries

The generated queries go through all Areas for one Neighborhood first, then for the next Neighborhood, until all combinations have been made. The output is the amount of overlap between each combination of specific Neighborhood and specific Area.

It is possible to have multiple clauses with an X (multiple queries with a Y) in the same query. In these situations, both X's are expanded in the same direction. Both (or all) clauses resolves to their first applicable ID. For the next generated query, only one of the generated queries resolves to the next ID, while the other(s) remain set to their first ID. This continues until the all applicable IDs of that clause have been used. Then, the other clause's ID is advanced by one, and the first clause starts at its first ID again, progressing until all its applicable IDs are exhausted.

For virtually all use-cases, it is more convenient to use an XY query than a multi X query, as it makes it easier to parse exactly which values have which meaning, and to structure the data for further calculations.

XA queries

The orange cell is named with an XA query for Areas with SEWER_STORAGE. The first columns retrieve all Area names and their SEWER_STORAGE values (if any). The XA query returns only the names of the Areas with that Attribute.

The X syntax can be expanded to be more selective on which Items to generate queries for, using the XA syntax. For example, rather than generating queries for all Areas in a Project, which may all serve a wildly different purpose, it is possible to retrieve only the Areas with a specific Attribute.

A query which would retrieve the names only of Areas which have a SEWER_STOARGE Attribute, marking them as Sewer Areas for use with the Water Overlay, could be constructed as follows:

SELECT_NAME_WHERE_AREA_IS_XA_SEWER_STORAGE

This will generate only queries for Areas which have an Attribute SEWER_STORAGE, in which "XA_SEWER_STORAGE" is in its entirety replaced by the relevant Area's ID. IDs of Areas which do not have the specified Attribute, or of which the Attribute's value is 0, are skipped in the same way that the IDs of deleted Items are skipped.

SORTed queries

Expanding on the XA syntax, it is also possible to sort the resulting Areas by the value of a specific attribute. This attribute may or may not be the same attribute as indicated in the first part of the XA syntax. For example:

SELECT_NAME_WHERE_AREA_IS_XA_SEWER_STORAGE_SORT_SEWER_PUMP_SPEED

This will generate only queries with a SEWER_STORAGE attribute, as before, but generate them not in the order of the Area's respective IDs, but in the order of the value of their SEWER_PUMP_SPEED attribute.

If an Item does have the XA Attribute, but is missing the SORT Attribute, then it is treated as if its Attribute on which to sort had a value of 0.

SORTed RELATION queries

Some Items may also have a relation to other Items. For example, an Area generated using the Border Area Generator will have relations with the two Areas it was generated between. It is possible to sort the generated queries of an XA query not on Attributes of the Item itself, but on the Attribute of the Item, it has a specific relation with. For example, in a Project where borders have been generated between all Areas with a SEWER_STORAGE Attribute:

SELECT_NAME_WHERE_AREA_IS_XA_BORDER_SORT_SEWER_STORAGE_RELATION_BORDER_A

This will retrieve all Areas with a BORDER Attribute (which is the default Attribute added to generated border Areas). For each border Area, it will look at the BORDER_A relation (which indicates another Area), and from that Area obtain the value of SEWER_STORAGE. The Areas with a BORDER Attribute are then sorted according to those retrieved values. In that order, queries are generated retrieving the name of the Areas which have a BORDER Attribute.

This is one of the most complex syntaxes but is also rarely applicable.

Index and timeframe

The orange cell is named with an XY query for calculated volumes of water per Neighborhood and per timeframe.

For most cases, the X applies to a replacement value for an Item's ID. Two clauses form an exception to this: the INDEX clause and the TIMEFRAME clause.

The following queries are allowed, and will be expanded according to the same rules as other X queries:

SELECT_ATTRIBUTE_WHERE_NAME_IS_BREACH_HEIGHT_AND_AREA_IS_0_AND_INDEX_IS_X

This will result in multiple queries, each retrieving a sequential value from the array of values of the BREACH_HEIGHT Attribute, of the Area with ID 0.

SELECT_GRIDAVG_WHERE_GRID_IS_3_AND_TIMEFRAME_IS_X

This will result in multiple queries, each retrieving the average result of a grid calculation for the entire map, for sequential timeframes.

This functionality is commonly combined with the ability to use an additional Y clause to retrieve a matrix of values for multiple Items.

XC queries

The XC syntax can be used to generate queries for all coverable Items which lay within the covering Item. This syntax behaves like an ID query, in that the query makes use of an implicit relation to another feature.

It can only be used in an Excel which is related to a specific Item, such as generated by an applied Template Panel. The covering Item is the Item referenced by the Excel Panel. The coverable Item is indicated with the XC clause.

An XC clause is interpreted to mean "all those Items of which the center point lies within the geometry of the Item this query is related to". For example, in an Excel which is used in a Template Panel which has been applied on Neighborhoods:

SELECT_NAME_WHERE_ADDRESS_IS_XC

This will expand into a series of queries to obtain the names of all addresses in the Neighborhood.

Coverable Items which can be related with XC-queries are:

  • Area
  • Address
  • Building
  • Neighborhood
  • Zone
  • Net Load
  • Net Line

Notes

See also