X query (Excel): Difference between revisions
(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 |
||
| Line 1: | Line 1: | ||
[[File:Xquery.jpg|framed|right|Example of what will happen with an X query with 3 neighborhoods.]] | [[File:Xquery.jpg|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, 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>. | 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>. | ||
{{clear}} | |||
==How X queries are resolved== | |||
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. | 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. | ||
| Line 7: | Line 9: | ||
{{Editor location|reset x}} | {{Editor location|reset x}} | ||
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 [[Item]]s 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. | |||
===XA | ===XY queries=== | ||
====Y queries==== | |||
===Multi X queries=== | |||
===XA queries=== | |||
To select specific Items (for example [[Neighborhoods|neighborhoods]] or [[Areas|areas]]) in combination with a X query, use a XA query. | To select specific Items (for example [[Neighborhoods|neighborhoods]] or [[Areas|areas]]) in combination with a X query, use a XA query. | ||
XA queries will select only Items with a specific [[Attribute|attribute]]. <br> | XA queries will select only Items with a specific [[Attribute|attribute]]. <br> | ||
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). | 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). | ||
====SORT==== | |||
===XA Queries=== | |||
Revision as of 11:16, 3 March 2021
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 Tygron Platform to dynamically create the queries for you. For any given query with uses an ID, you can replace the ID with X, with the result being a query such as SELECT_UNITS_WHERE_NEIGHBORHOOD_IS_1 turning into SELECT_UNITS_WHERE_NEIGHBORHOOD_IS_X.
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.
XY queries
Y queries
Multi X queries
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).