The XYTableGroupBy function

The XYTableGroupBy function aggregates data in a two-dimensional table that is stored as a delimited string.

When MyWraps2 is used to retrieve data from the ExcelWraps database, it returns the result either in a single column called an XTable, or in a two-dimensional format called an XYTable. An XYTable is a two-dimensional data structure represented as a text string, with the columns separated by semicolons and the rows separated by the pipe (vertical bar) character.

Example: the simple table below:

would be stored like this as an XYTable:

“Fleet;Make;Depot|800-01;2012;Doncaster|800-01;2015;Doncaster|800-02;2018;Temple Mills|800-03;2020;East Ham Depot”

Making database calls to aggregate data is convenient, but may consume unnecessary resources if the source data is already present in an XYTable. The XYTableGroupBy function makes it easy to count the number of cars per depot in the table. You can group the table on any relevant column and then make calculations on any column in the table, e.g. sum the values or determine the range of the values in the column.

Function reference

Example: sum

=XYTableGroupBy("800-01;2012;Doncaster|800-01;2015;Doncaster|800-02;2018;Temple Mills|800-03;2020;East Ham Depot",1,"sum",2)

In the example table above, you could use XYTableGroupBy to sum the number in column 2 per fleet. The result of the operation is returned as an XYTable with the group identifier in column 1 and the result of the aggregation in column 2. For this sum operation, the function would return

800-01;4027|800-02;2018|800-03;2020

Example: count

=XYTableGroupBy("800-01;2012;Doncaster|800-01;2015;Doncaster|800-02;2018;Temple Mills|800-03;2020;East Ham Depot",1,"count",2)

In the example table above, you could use XYTableGroupBy to count the number of rows per fleet. The result of the operation is returned as an XYTable with the group identifier in column 1 and the result of the aggregation in column 2. For this count operation, the function would return

800-01;2|800-02;1|800-03;1

Example: counta

=XYTableGroupBy("800-01;2012;Doncaster|800-01;2015;Doncaster|800-02;;Temple Mills|800-03;2020;East Ham Depot",1,"counta",2)

In the example table above, you could use XYTableGroupBy to count the number of rows per fleet that have any value. The result of the operation is returned as an XYTable with the group identifier in column 1 and the result of the aggregation in column 2. For this counta operation, the function would return

800-01;2|800-03;1

Note that the count functions do not return groups that have a count of 0.

Format and parameters

=XYTableGroupBy(xyTable,groupColumn,aggregateFunction,aggregateColumn)

xyTable (required)

Points to the cell where the XYTable resides.

groupColumn (optional)

Enter the index of the column that will be used group the table when aggregating, e.g. 1 if you want a sum for each value in the first column. If you omit the group column, the entire values of the aggregateColumn will be aggregated and the function returns blank value for group column i.e. “;30”, therefore remove “;” to get the actual aggregate value.

aggregateFunction (required)

Specify the function that you want to apply on the aggregateColumn, as a string value. The following aggregations are supported:

• “count” – returns the number of rows in each group that have a numeric value for the aggregateColumn. If a group has a count of 0, it is not included in the result, as shown in the example for counta above.
• “counta” – returns the number of rows in each group that have any value for the column. A cell that is unset or contains an empty string (“”) is not counted. A cell that contains blank text ” ” or a zero value 0 is not counted. If a group has a count of 0, it is not included in the result, as shown in the example for counta above.
• “countblank” – returns the number of rows in each group that have no value for the column, including those that are unset or return an empty string (“”). A cell that contains blank text ” ” or a zero value 0 is also counted. If a group has a count of 0, it is not included in the result, as shown in the example for counta above.
• “sum” – returns the sum of the values for each group.
• “min” – returns the lowest value for each group.
• “max” – returns the highest value for each group. .

aggregateColumn (required)

Enter the index of the column that will be used for the aggregation function, e.g. 2 if you want the sum for the second column.