Returns a table showing distinct groups of values found in one or more columns. An extra column is appended with the count of the number of rows per distinct group.
SppTableGroupByColumnsWithCount([Table Array],[Group By Columns])
Where:
Table Array Table Array is a Table Array (such as the data in a standard table, or the result of a QueryDataValues function.
Group By Columns is the indexes of the columns to group by as a pipebar (|) delimited string, for example 2|7|5. Number starting at 1 for the first column.
| Rule | Result | Meaning |
|---|---|---|
| SppTableGroupByColumnsWithCount(DwLookupCasingOrders,"1|4") | {"Part","Primary Color","Count"; "Casing","Red",2; "Frame","Black",2; "Chassis","Blue",2} | Will return an Array showing distinct values from the Part (first) column and Primary Color (fourth) column, with a Count column appended. |
See Example Result Viewed as a Table below to see how the result of this example looks when viewed as a table.
The data below exists as a Table in DriveWorks named CasingOrders (DwLookupCasingOrders)
| Part | Price | Quantity | Primary Color | Secondary Color |
|---|---|---|---|---|
| Casing | 210 | 15 | Red | Red |
| Casing | 200 | 25 | Red | Blue |
| Frame | 250 | 5 | Black | Orange |
| Chassis | 195 | 30 | Blue | Teal |
| Chassis | 230 | 10 | Blue | Blue |
| Frame | 190 | 50 | Black | Black |
The array returned by the rule SppTableGroupByColumnsWithCount(DwLookupCasingOrders,"1|4") will look like:
| Part | Primary Color | Count |
|---|---|---|
| Casing | Red | 2 |
| Frame | Black | 2 |
| Chassis | Blue | 2 |