Group data across multiple rows with the Group Attributes data service
Group data across multiple rows with the Group Attributes data service in Productsup.
Introduction
The Group Attributes data service lets you group information from different rows in your feed. You can do the following actions to the values for products grouped by some common attribute:
Combine values. See Concat method.
Count values. See Count method.
Identify the highest or lowest value. See Max and Min methods.
Sum values. See Sum method.
The service works based on the SQL function GROUP BY. See SQL Tutorial for more information.
Prerequisites
To set up the Group Attributes data service, you need:
A product identifier. See Set a product identifier for more information.
At least two (2) columns in your feed if you want to use the Concat grouping method.
Add the Group Attributes data service
Go to Data Services from your site's main menu and select ADD SERVICE.
Search for Group Attributes, select Add, rename the service as desired, and enter a prefix that should prepend the result column name.
You can leave the default column prefix
___.
Choose the stage containing the columns you want to group in Service Data Level and select Add.
Note
The Group Attributes data service is available for the import and intermediate stages.
Select the column containing the values you wish to group from the drop-down menu Column containing the values.
Note
If you choose Import in Service Data Level in Step 3, the drop-down menu Column containing the values displays the columns of your import stage. If you choose Intermediate, the drop-down menu shows your intermediate-stage columns.
Optionally, in the drop-down menu Import column that you want to group by (optional), select the column by which the platform should determine the common attribute.
Tip
To choose several columns, hold the CTRL (or CMD) key while selecting.
Warning
If you choose the Concat method further in the setup process, you must select a column in this drop-down menu not to cause data processing errors.
Enter the desired name of the result column into the Result column field.
Note
The system creates a new column to output the grouped data into it without overwriting the existing columns. The platform prepends the Result column's name with the prefix you set in Step 2.
Select the grouping method from the Method drop-down menu.
Note
For the Concat method only:
In Delimeter, select the delimiter that separates the grouped values in the result column.
Activate Concat unique if you don't want to display duplicate values.
Select Save.
Add another Group Attributes data service to your site and repeat steps starting with Step 4 to group more values.
For the platform to process a new data service, select Run in the top-right corner of your site's view.
Examples of the output for different group methods
During setting up the data service, you can select from five (5) different data-grouping methods from the Method drop-down menu:
Concat
Count
Max
Min
Sum
Here is a sample feed we can use to show how each grouping method works:
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 method
Use the Concat method to combine all values from different rows in one column.
Example:
To concat sizes for products with the same item_group_id, use the following setup:
Column containing the values:
sizeImport column that you want to group by (optional):
item_group_idResult column:
concat_sizesDelimiter:
,COMMAConcat unique: off
Using this setup, the system groups all sizes of the products with the same item_group_id in a newly created ___concat_sizes column:
id | item_group_id | stock | size | color | discount_in_percent | ___concat_sizes |
|---|---|---|---|---|---|---|
1 | 123 | 4 | S | red | 10 | S,S,L |
2 | 123 | 2 | S | blue | 15 | S,S,L |
3 | 123 | 1 | L | blue | 25 | S,S,L |
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 |
Note
The system combines the data without any spaces between characters.
Count method
Use the Count method to count the number of product variations.
Example:
To count the number of product variations with the same item_group_id, use the following setup:
Column containing the values:
item_group_idImport column that you want to group by (optional):
item_group_idResult column:
count_variations
Using this setup, the system counts all product variations with the same item_group_id and records the result in a newly created ___count_variations column:
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 and Min methods
Use the Max or Min methods to find and display the highest or lowest value.
Example:
To return the highest discount among all products with the same item_group_id, use the following setup:
Column containing the values:
discount_in_percentImport column that you want to group by (optional):
item_group_idResult column:
max_discount
Using this setup, the system compares discounts of all products with the same item_group_id and records the highest value in all rows of a newly created ___max_discount column for the products with the same item_group_id:
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 method
Use the Sum method to return the numerical sum of products matching the selected conditions.
Example:
To sum the stock for products with the same item_group_id and color, use the following setup:
Column containing the values:
stockImport column that you want to group by (optional):
item_group_idandcolorResult column:
stock_sum
Using this setup, the system sums all products with the same item_group_id and color and records the numbers into a newly created ___stock_sum column:
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 | 4 |
6 | 456 | 7 | 38 | green | 5 | 9 |
7 | 456 | 2 | 40 | green | 10 | 9 |
Edit the Group Attributes data service
Go to Data Services from your site's main menu.
Search for your data service.
Select the cogwheel icon (⚙) next to the desired data service to edit settings.
Select Save.
Delete the Group Attributes data service
Go to Data Services from your site's main menu.
Select the cogwheel icon (⚙) next to the desired data service.
In the Danger Area panel, select Remove this service.
Select Yes.