How to summarize quantities of sold products by grouping by category

How To
,
1

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

image
image1343×563 52.2 KB

image
image503×1030 38.8 KB

image
image511×1025 54 KB

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"
		}
	]
}
2

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:
Screenshot_2023-10-07_111039

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!

2 Likes
3

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

image
image891×746 61.8 KB

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

image
image881×901 23.5 KB

Iterator

image
image882×797 22.1 KB

Compose a string

image
image498×991 66.3 KB

4

Welcome to the Make community!

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

Screenshot_2024-01-07_170140
Screenshot_2024-01-07_1701401265×350 47.9 KB

1. Iterate and Set variables

Screenshot_2024-01-07_170151
Screenshot_2024-01-07_170151732×614 44.8 KB

2. Array aggregate, group by category name

Screenshot_2024-01-07_170113
Screenshot_2024-01-07_170113683×676 60 KB

3. Text aggregate and sum categories in single step

Screenshot_2024-01-07_170132
Screenshot_2024-01-07_170132752×632 47.2 KB

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

3 Likes
5

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 ?

6

Yes, that field isn’t important

2 Likes
7

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.

1 Like
8

it Works perfectly.
Thanks a lot

image
image1559×890 88.1 KB

2 Likes
9

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