Group data across multiple rows with the Group Attributes service
Group data across multiple rows with the Group Attributes service in Productsup.
If you want to group information that is spread over different rows, not columns, you can use the Group Attributes service to reach the desired result. This service gives you the possibility to count, sum, and concatenate or concat, which means combining a string, text, or other data without any gaps in between characters. Or you can identify the minimum and maximum values over a group of products.
The service is based on the SQL GROUP BY function. To learn more, see SQL Tutorial.
Note
In order to use this service, please ensure you have set a product identifier.
Add the Group Attributes service
In order to add the Group Attributes service, you should:
Navigate to your site
Navigate to Data Services on the left-hand tab
Click add service
Add the Group Attributes service
Give the service a name (if desired)
Define a custom column prefix (if desired)
Select whether to use the service on the import or intermediate level
this should be where the data in which you wish to group
Click add
Select the column containing the values which you wish to group
Select the import column that you want to group by
Define a result column where your grouped data will be outputted
Select the method (examples below)
Click save
You can add multiple instances of this service by repeating the above process.
Examples of group methods
There are five different methods on which you can group your data. They will be explained in detail below.
The explanations will go through scenarios and the required settings needed to achieve them. Each example will be based on this starting import feed:
id | item_group_id | stock | size | color | discount_in_percent |
|---|---|---|---|---|---|
1 | 123 | 4 | S | red | 10 |
2 | 123 | 2 | S | blue | 15 |
3 | 123 | 1 | L | blue | 25 |
4 | 456 | 5 | 38 | black | 7 |
5 | 456 | 4 | 38 | blue | 15 |
6 | 456 | 7 | 38 | green | 5 |
7 | 456 | 2 | 40 | green | 10 |
Concat: group method
You can use concat in order to append grouped data in one column. Our goal for the below example is to concat sizes for products with the same item_group_id.
Method
Value column: size
Group column: item_group_id
Result column: concat_sizes
Delimiter: comma. This option is only available for the concat method and lets you choose the separator for your grouped data.
Concat unique: no. This option lets you decide if you want to display duplicated values only once, or whether each instance of a value should be displayed.
Result
id | item_group_id | stock | size | color | discount_in_percent | ___concat_sizes |
|---|---|---|---|---|---|---|
1 | 123 | 4 | S | red | 10 | S,S,M |
2 | 123 | 2 | S | blue | 15 | S,S,M |
3 | 123 | 1 | L | blue | 25 | S,S,M |
4 | 456 | 5 | 38 | black | 7 | 38,38,38,40 |
5 | 456 | 4 | 38 | blue | 15 | 38,38,38,40 |
6 | 456 | 7 | 38 | green | 5 | 38,38,38,40 |
7 | 456 | 2 | 40 | green | 10 | 38,38,38,40 |
Count: group method
You can use the count group method in order to count the number of variations of a product that appears. Our goal for the below example is to count the number of products with the same item_group_id.
Method
Value column: item_group_id
Group column: item_group_id
Result column: count_variations
Result
id | item_group_id | stock | size | color | discount_in_percent | ___count_variations |
|---|---|---|---|---|---|---|
1 | 123 | 4 | S | red | 10 | 3 |
2 | 123 | 2 | S | blue | 15 | 3 |
3 | 123 | 1 | L | blue | 25 | 3 |
4 | 456 | 5 | 38 | black | 7 | 4 |
5 | 456 | 4 | 38 | blue | 15 | 4 |
6 | 456 | 7 | 38 | green | 5 | 4 |
7 | 456 | 2 | 40 | green | 10 | 4 |
Max/min: group method
You can use the max or min group methods in order to find and display the product with the highest or lowest value in the column you define. Our goal for the below example is to return the highest appearing discount within all products with the same item_group_id.
Method
Value column: discount
Group column: item_group_id
Result column: max_discount
Result
id | item_group_id | stock | size | color | discount_in_percent | ___max_discount |
|---|---|---|---|---|---|---|
1 | 123 | 4 | S | red | 10 | 25 |
2 | 123 | 2 | S | blue | 15 | 25 |
3 | 123 | 1 | L | blue | 25 | 25 |
4 | 456 | 5 | 38 | black | 7 | 15 |
5 | 456 | 4 | 38 | blue | 15 | 15 |
6 | 456 | 7 | 38 | green | 5 | 15 |
7 | 456 | 2 | 40 | green | 10 | 15 |
Sum: group method
You can use the sum group method in order to return the numerical sum for a column for the grouped products. Our goal for the below example is to sum the stock for products with the same item_group_id and color.
Method
Value column: stock
Group column: item_group_id and color
Result column: stock_sum
Result
id | item_group_id | stock | size | color | discount_in_percent | ___stock_sum |
|---|---|---|---|---|---|---|
1 | 123 | 4 | S | red | 10 | 4 |
2 | 123 | 2 | S | blue | 15 | 3 |
3 | 123 | 1 | L | blue | 25 | 3 |
4 | 456 | 5 | 38 | black | 7 | 5 |
5 | 456 | 4 | 38 | blue | 15 | 5 |
6 | 456 | 7 | 38 | green | 5 | 9 |
7 | 456 | 2 | 40 | green | 10 | 9 |
Edit an existing Group Attributes service
In order to edit settings for your Group Attributes service, you should:
Navigate to your site
Navigate to Data Services on the left-hand tab
Click on the settings wheel
Delete an existing Group Attributes service
In order to delete your Group Attributes service, you should:
Navigate to your site
Navigate to Data Services on the left-hand tab
Click on the settings wheel
Scroll to the bottom of the page and click remove this service