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 an Excel file with one or more X queries is uploaded, the Tygron Platform 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 X 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 Tygron Platform. 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.
The original Excel file is saved by the Tygron Platform. 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 Tygron Platform. It is possible to trigger the Tygron Platform to do this, by using the "Reset X Queries" option in the editor.
It is important to note that queries are created from X queries only while to project is being edited. During a Test run or other Session, the queries resulting from an X query are not regenerated. This means adding more Items matching an X query during a session will not lead to the related queries being generated.
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