How to summarize quantities of sold products by grouping by category

Ludovic_M · January 7, 2024, 1:55am

Hi,

I have a list of sold products and quantities.
In my Json, variable “a1” = Product Name = Category+Size
I want to summarize (sum) the sold quantities by Category.
Is there a module to do this ?

From :

{“productName”:“CategoryA_size1”,“quantity”:“QA1”},
{“productName”:“CategoryA_size2”,“quantity”:“QA2”},
{“productName”:“CategoryA_size3”,“quantity”:“QA3”},
{“productName”:“CategoryB_size1”,“quantity”:“QB1”},
{“productName”:“CategoryB_size4”,“quantity”:“QB4”},
{“productName”:“CategoryB_size6”,“quantity”:“QB6”}

To :
{“CategoryName”:“CategoryA”,“quantity”:“sum(QA)”},
{“CategoryName”:“CategoryB”,“quantity”:“sum(QB)”}

Actually
I have defined this scenario

I’ve managed to extract the categories and quantities to sum.
But I can’t manage to group by category and sum quantities.
Any help would be appreciated.
Thank you

Json example

{
	"lineItems": [
		{
			"a1": "800375_7010_S",
			"l1": "Tee-shirt Philibert Charmant écru e",
			"q1": "4",
			"u1": "pce",
			"p1": "19.6",
			"m1": "78.4"
		},
		{
			"a1": "800375_7010_L",
			"l1": "Tee-shirt Philibert Charmant écru e",
			"q1": "4",
			"u1": "pce",
			"p1": "19.6",
			"m1": "78.4"
		},
		{
			"a1": "800375_7010_M",
			"l1": "Tee-shirt Philibert Charmant écru e",
			"q1": "4",
			"u1": "pce",
			"p1": "19.6",
			"m1": "78.4"
		},
		{
			"a1": "800376_1290_XL",
			"l1": "Tee-shirt philibert bleu recyclé es",
			"q1": "3",
			"u1": "pce",
			"p1": "19.6",
			"m1": "58.8"
		},
		{
			"a1": "800376_1290_M",
			"l1": "Tee-shirt philibert bleu recyclé es",
			"q1": "4",
			"u1": "pce",
			"p1": "19.6",
			"m1": "78.4"
		},
		{
			"a1": "800376_1290_S",
			"l1": "Tee-shirt philibert bleu recyclé es",
			"q1": "4",
			"u1": "pce",
			"p1": "19.6",
			"m1": "78.4"
		},
		{
			"a1": "800376_1290_L",
			"l1": "Tee-shirt philibert bleu recyclé es",
			"q1": "4",
			"u1": "pce",
			"p1": "19.6",
			"m1": "78.4"
		},
		{
			"a1": "800596_7010_S",
			"l1": "Tee-Shirt Baptiste Coq écru en coto",
			"q1": "4",
			"u1": "pce",
			"p1": "19.6",
			"m1": "78.4"
		},
		{
			"a1": "800596_7010_M",
			"l1": "Tee-Shirt Baptiste Coq écru en coto",
			"q1": "4",
			"u1": "pce",
			"p1": "19.6",
			"m1": "78.4"
		},
		{
			"a1": "800596_7010_XL",
			"l1": "Tee-Shirt Baptiste Coq écru en coto",
			"q1": "3",
			"u1": "pce",
			"p1": "19.6",
			"m1": "58.8"
		},
		{
			"a1": "800596_7010_L",
			"l1": "Tee-Shirt Baptiste Coq écru en coto",
			"q1": "4",
			"u1": "pce",
			"p1": "19.6",
			"m1": "78.4"
		},
		{
			"a1": "719067_1430_M",
			"l1": "Sweat David Gentleman bleu en coton",
			"q1": "4",
			"u1": "pce",
			"p1": "34.8",
			"m1": "139.2"
		},
		{
			"a1": "719067_1430_L",
			"l1": "Sweat David Gentleman bleu en coton",
			"q1": "4",
			"u1": "pce",
			"p1": "34.8",
			"m1": "139.2"
		},
		{
			"a1": "719067_1430_S",
			"l1": "Sweat David Gentleman bleu en coton",
			"q1": "4",
			"u1": "pce",
			"p1": "34.8",
			"m1": "139.2"
		},
		{
			"a1": "719067_1430_XL",
			"l1": "Sweat David Gentleman bleu en coton",
			"q1": "3",
			"u1": "pce",
			"p1": "34.8",
			"m1": "104.4"
		},
		{
			"a1": "707876_8041_M",
			"l1": "Sweat Basile brodé gris recyclé",
			"q1": "4",
			"u1": "pce",
			"p1": "34.8",
			"m1": "139.2"
		},
		{
			"a1": "707876_8041_S",
			"l1": "Sweat Basile brodé gris recyclé",
			"q1": "4",
			"u1": "pce",
			"p1": "34.8",
			"m1": "139.2"
		},
		{
			"a1": "707876_8041_XL",
			"l1": "Sweat Basile brodé gris recyclé",
			"q1": "3",
			"u1": "pce",
			"p1": "34.8",
			"m1": "104.4"
		},
		{
			"a1": "707876_8041_L",
			"l1": "Sweat Basile brodé gris recyclé",
			"q1": "4",
			"u1": "pce",
			"p1": "34.8",
			"m1": "139.2"
		},
		{
			"a1": "713722_1430_S",
			"l1": "Tee-shirt Philibert Gentleman bleu ",
			"q1": "4",
			"u1": "pce",
			"p1": "19.6",
			"m1": "78.4"
		},
		{
			"a1": "713722_1430_M",
			"l1": "Tee-shirt Philibert Gentleman bleu ",
			"q1": "4",
			"u1": "pce",
			"p1": "19.6",
			"m1": "78.4"
		},
		{
			"a1": "713722_1430_L",
			"l1": "Tee-shirt Philibert Gentleman bleu ",
			"q1": "4",
			"u1": "pce",
			"p1": "19.6",
			"m1": "78.4"
		},
		{
			"a1": "713722_1430_XL",
			"l1": "Tee-shirt Philibert Gentleman bleu ",
			"q1": "3",
			"u1": "pce",
			"p1": "19.6",
			"m1": "58.8"
		},
		{
			"a1": "800607_2550_S",
			"l1": "Tee-shirt Barthélémy vélo vert en c",
			"q1": "4",
			"u1": "pce",
			"p1": "19.6",
			"m1": "78.4"
		},
		{
			"a1": "800607_2550_M",
			"l1": "Tee-shirt Barthélémy vélo vert en c",
			"q1": "4",
			"u1": "pce",
			"p1": "19.6",
			"m1": "78.4"
		},
		{
			"a1": "800607_2550_L",
			"l1": "Tee-shirt Barthélémy vélo vert en c",
			"q1": "4",
			"u1": "pce",
			"p1": "19.6",
			"m1": "78.4"
		},
		{
			"a1": "800607_2550_XL",
			"l1": "Tee-shirt Barthélémy vélo vert en c",
			"q1": "3",
			"u1": "pce",
			"p1": "19.6",
			"m1": "58.8"
		},
		{
			"a1": "800375_7010_XL",
			"l1": "Tee-shirt Philibert Charmant écru e",
			"q1": "3",
			"u1": "pce",
			"p1": "19.6",
			"m1": "58.8"
		}
	],
	"echeancier": [
		{
			"Echeance_dt": "22/08/2023",
			"Echeance_mt": "995,54",
			"Echeance_no": "1",
			"Echeance_status": "A régler"
		},
		{
			"Echeance_dt": "21/09/2023",
			"Echeance_mt": "995,54",
			"Echeance_no": "2",
			"Echeance_status": "A régler"
		},
		{
			"Echeance_dt": "21/10/2023",
			"Echeance_mt": "1 025,71",
			"Echeance_no": "3",
			"Echeance_status": "A régler"
		}
	]
}

samliew · January 7, 2024, 5:08am

Welcome to the Make community!

All we see are a1, l1, q1, u1, p1. Where are the “productName”, “Category”, “quantity” fields in your output bundle?

Why are you using Compose a String instead of an aggregator, like Array Aggregator with “group by” field?

1. Screenshots of module fields and filters

Please share screenshots of relevant module fields and filters in question? It would really help other community members to see what you’re looking at.

You can upload images here using the Upload icon in the text editor:

2. Scenario blueprint

Please export the scenario blueprint file to allow others to view the mappings and settings. At the bottom of the scenario editor, you can click on the three dots to find the Export Blueprint menu item.

Screenshot_2023-08-24_230826
^{(Note: Exporting your scenario will not include private information or keys to your connections)}

Uploading it here will look like this:

blueprint.json (12.3 KB)

Following these steps will allow others to assist you here. Thanks!

Ludovic_M · January 7, 2024, 8:57am

Hi,

As an input, the Category variable is not explicitly present.
But Category is included in productName as a prefix

producName = variable a1
quantity = variable q1
Category is include in productName

for example :

Concerning first item
“a1”: “800375_7010_S”
“q1”: “4”,

productName = “800375_7010_S”
quantity = “4”
Category = “800375_7010”
(size = “S”)

I extract variable Category by using a Compose a string module and by using Subtring fonction

If this makes it possible to drastically reduce the number of modules, I can add a new variable Category in the Json to delete extraction step, to extract the data from the productName.

Output Bundle
Parse Json

Iterator

Compose a string

samliew · January 7, 2024, 9:37am

Welcome to the Make community!

Yes, that is possible. You’ll need a minimum of four modules:

1. Iterate and Set variables

2. Array aggregate, group by category name

3. Text aggregate and sum categories in single step

Give it a go and let us know if you have any issues!

Ludovic_M · January 7, 2024, 11:34am

Thanks.
I was aware that I had a parsing problem with the compose a string module.
So, it’s more appropriate to use Set multiple variables module.
And if I add Category as input to the json, I end up needing just one module instead of four.
I think it would be a good idea to add it, as it will have less impact on my operating quota.
In Text aggregator module, there is no parameter in the section Group by. Right ?

samliew · January 7, 2024, 12:25pm

Yes, that field isn’t important

Ludovic_M · January 7, 2024, 2:12pm

Quota is reached because of to much invalid tests and with a too big json.
I have to wait before to make a new test. But all is clear.
Thanks for all.
I’ll let you know when I’ve completed the tests.

Ludovic_M · January 10, 2024, 6:14pm

it Works perfectly.
Thanks a lot

Ludovic_M · January 10, 2024, 11:17pm

Tofinalize the automation, which module allows to add the result Json to the initial Json ?

Topic		Replies	Views
Aggregating all products from bundles in one line How To aggregators , mapping , arrays	11	1930	March 8, 2024
How to aggregate number values in part of JSON Collection by condition? How To arrays	3	20	November 19, 2024
Counting and categorizing items in an array How To aggregators , arrays	9	8557	October 10, 2023
Aggregator Fields Problem (Not Showing) How To	1	346	September 7, 2023
Getting the SUM of sales for the week for each product How To google-sheets	4	419	June 23, 2023