GROUP or Aggregate data

[Saivinod]

Hi there,
Quick query. Suppose, I have of my purchase list like this:
BRAND A - soap - 3
BRAND B- soap - 4
BRAND C - soap - 9
BRAND D- sanitizer - 7
BRAND E- sanitizer- 6

How do I aggregate the totals based on soap and sanitizer 16 and 13 respectively using a KNIME node. Any simple solutions would be welcome.

This is just a sample example; Actually I have data running to 500+ items and I need to aggregate/groupby few family of items and calculate the stats for the same.

[elsamuel]

I would separate your list into 3 columns, then use the GroupBy node to group on column 2 and aggregate column 3 using sum.

[Saivinod]

Thank You
|RAN100|Soap|1|
|RAN123|Soap|4|
|RAN145|Soap|5|
|RAN201|San|6|
|RAN203|San|4|
It would be helpful as to how to achieve this:
SUM of RAN1* =10
SUM of RAN2*=10

[Saivinod]

Also I need help as to how to exactly work with options in groupby node. Any examples/ videos

[ipazin]

Hello @Saivinod,

do you have any sample data you can share? And desired output? That way would be easier to understand your data structure and desired outcome.

Regarding workflow examples you can check KNIME Hub (groupby – KNIME Hub) and for videos you’ll find couple of them on YouTube (https://www.youtube.com/results?search_query=groupby+knime).

Br,
Ivan

[Saivinod]

Sample Data for Groupby.xlsx (8.3 KB)
Here I want to Sum all the quantity based on product ID starting with RAN1
and RAN2 separately.
Thank You

[elsamuel]

I would use a string manipulation node to trim the product IDs down to a stem consisting of the leftmost 4 letters, then use the GroupBy node to group by that stem.

[Saivinod]

Thank You, It is working.
I had to use pattern-based group-by option with right connotation.