With this option it is possible to merge rows containing a column with the same data. This is an optional feature. This function is called: Group By. In the example below (note the supplier: ABC Corporation) a number of rows have been merged based on the following settings:
Before merging:
After merging:
It groups based on the VendorsName column, and then takes the highest value of all other columns. So there's only one row left.
Of course you may not want the maximum value for the other columns (all columns, or only selected columns) to be selected. That is why you can also use the following other 'aggregate functions' per column:
- Count
In the remaining line it is shown in the relevant column in how many of the total number of merged lines there was a value in this column. In the example above, this will be 6 for VendorContactEmail.
- AVG
In the remaining line, the average of all merged lines is shown in the relevant column. In the example below, we applied GROUP BY with the aggregate function AVG to the VendorUserId column. Then an average of all VendorUserId values of the supplier ABC Corporation is shown.
- sum
In the remaining line, a list of all merged lines is shown in the relevant column. In the example below, we applied GROUP BY with the aggregate function SUM to the VendorId column. Now a list of all VendorIds is shown per supplier.
- Min
In the remaining line, the minimum value that has occurred in all lines of the supplier is shown in the relevant column. In the example below, we applied the GROUP BY function MIN to the VendorContactDisplayName column. Now, when there are only alphabetical values, in the remaining line the value in VendorContactDisplayName which would appear first in alphabetical order is shown. But numbers always come before alphabetic characters, and there is also a contact with a '1' as VendorContactDisplayName, therefore 1 is returned as a result. If only values with numbers exist, the lowest number is shown.
- Max
In the remaining line, the maximum value that has occurred in all lines of the supplier is shown in the relevant column. In the example below, we have applied the GROUP BY function MAX to the VendorsId column. Now, when there are only alphabetical values, the value in VendorsId that would appear last in alphabetical order is shown in the remaining line. Alphabetic characters always come after numeric characters, so when a column contains fields with only numeric and only alphabetic values, a field with only numeric values can never result from a GROUP BY MAX. If only values with numbers exist, the highest number is shown. In this case, the VendorsId for a vendor is always one and the same number, so the result of a MIN or MAX aggregate function will be the same:
Group by: VendorsName
- Click in the left field on the column you want to use as a base to get the merged rows. If you select VendorsName here, then per supplier all rows are merged. So you are left with one line per supplier.
- Click Add.
Aggregate function: Max
- In the left field, select the column where you want the aggregate function to be applied.
- Select an aggregate function by clicking on one of the options on the right.