Joining and grouping when one Joining column contains additional IDs

Dear all,

I have a list, basically containing an ID and a value column, e.g.:
ID_input____Value
1X________12
2Y________10
3Y________20

The IDs were transformed to another type of IDs, this gives me:
ID____ID_input
PUZ__1X
PAZ__2Y,3Y

The new list contains the type of IDs I need for further processing the data, but of course I would like to retrieve the values from the initial list. Using for instance the joiner node - full outer join yields:

ID____ID_input____Value
PUZ__1X________12
PAZ__2Y,3Y______?
?____2Y________10
?____3Y________20

What I would like to have is something like find 2Y and 3Y in “initial list” and sum values.

Is there a way to do this?

Kind regards

Kiro

Hi there Kiro,

If I got you right I would suggest to you to prepare your data in following format:

ID_input____Value_____ID
1X________12________PUZ
2Y________10________PAZ
3Y________20________PAZ

Then simply by using GroupBy node where grouping column should be ID and aggregation columns are ID_Input with concatenate aggregation and Value with sum aggregation.

Br,
Ivan

1 Like

Hi,

Use Cell Splitter and Ungroup nodes to transform this:
ID____ID_input
PUZ__1X
PAZ__2Y,3Y

to this:

ID____ID_input
PUZ__1X
PAZ__2Y
PAZ__3Y

After that, join the first table (use ID_input as the joining column) to have values as well.
Now you can follow Ivan’s suggestion:

Best,
Armin

2 Likes

@ipazin, @armingrudd, Thank you both for your help!

Yes @armingrudd, that was exactly the method I was looking for.

Kind regards
Kiro

2 Likes

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.