X query (Excel)
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 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
You would need multiple of such queries to retrieve the sizes of all Neighborhoods. Instead, you can 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.
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 theNeighborhood with ID 0, followed directly in the next row by a query for theNeighborhood 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.
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.
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.
XY queries
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.
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.
Multi X queries
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.
XA queries
To select specific Items (for example neighborhoods or areas) in combination with a X query, use a XA query.
XA queries will select only Items with a specific attribute.
Example: SELECT_ID_WHERE_AREA_IS_X will show all areas, using SELECT_ID_WHERE_AREA_IS_XA_PEIL will only show the areas with the attribute PEIL (where the value of PEIL is not 0).
SORT
Index and timefame