Returns a table showing distinct groups of values found in one or more columns. An extra column is appended with the maximum value found in the specified column, in rows, per distinct group.
SppTableGroupByColumnsWithMax([Table Array],[Group By Columns],[Max Column])
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.
Max Column is the column in the Table Array that a maximum value for each unique value combination of Group By Columns will be extracted for. Number starting at 1 for the first column.
| Rule | Result | Meaning |
|---|---|---|
| SppTableGroupByColumnsWithMax(DwLookupCasingOrders,"1|4",2) | {"Part","Primary Color","Max(Price)"; "Casing","Red",210; "Frame","Black",250; "Chassis","Blue",230} | Will return an Array showing distinct values from the Part (first) column and Primary Color (fourth) column, and append the Max Price (second) column. |
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 SppTableGroupByColumnsWithMax(DwLookupCasingOrders,"1|4",2) will look like:
| Part | Primary Color | Max(Price) |
|---|---|---|
| Casing | Red | 210 |
| Frame | Black | 250 |
| Chassis | Blue | 230 |