X query (Excel)

From Tygron Support wiki
Jump to navigation Jump to search
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

Retrieved from "https://support.tygron.com/w/index.php?title=X_query_(Excel)&oldid=53947"