Table Joining with Addition Functions

KNIME Analytics Platform
1

I have two tables. They both have two columns. A category column and then a count for that category.

Example:
Table #1

Column 1 Column 2
Abuse 45

Table #2

Column 1 Column 2
Abuse 3

I want to join these two tables so that the category is summed.
In this example Abuse would be summed at 48.

Any non-matching rows would simply get their own row and count.

So in this example there is clearly two rows between the two tables with a category of Abuse.
However, maybe thereā€™s a row category of ā€˜Antagonismā€™ that doesnā€™t have any other match. It gets its own row.

Jimbo

2

Use a Concatenate node, then a Groupby node. In the latter, add Column 1 to the grouping columns, and then in the aggregation tab add Column 2, and select ā€˜Sumā€™ as the aggregation method, and at the bottom of the dialog, select the ā€˜Keep Incoming Column Namesā€™ option

Steve

3

Roger that -
Will look those up.

Thanks sir~

4

That was close, but the abuse is still at 45 when it should be at 48ā€¦

Jimbo

5
  1. What is shown exactly as result?
  • Just ā€œAbuse | 45ā€ => Please check output of concatenate node.
  • Both ā€œAbuse | 45ā€ and ā€œAbuse | 3ā€ => Check if both fields are really spelled the same (including leading / trailing spaces, special characters etc.).
  1. What kind of aggregation did you specify in the group by node?
6

How about to use the concatenate node and just put the two tables together and then use the group by node? And as @Patrick1974 mentioned: check for blanks or spellings. There should be a string manipulation node for that.

7

Hereā€™s what the table looks like after concatenate

image
image463Ɨ728 20.1 KB

8

Iā€™m thinking Iā€™m probably going to have to manually input the data after I separate the initial tags columnā€¦

9

Your problem looks like you have 2 different column names in the two tables, Either rename the second table to match the first (Column Rename node), or use two Column Merger nodes, one for your categories columns and one for your counts columns,
Steve

1 Like
10

I didnā€™t even think to rename the columns. Iā€™ll give that a try.

Jimbo

11

Vern - looks like the conc is working now?
However I canā€™t seem to get it to sum rightā€¦

image
image339Ɨ640 15.3 KB

12

Oh i think I see itā€¦looks like thereā€™s a space in the category column somehow added from the second tableā€¦

13

holy crap - got it to workā€¦it was the white space that was stopping the group from working right. ha ~

image
image382Ɨ503 13.5 KB

Thanks for the help folks!

Jimbo

2 Likes
14

Now I just have to create this huge meta node to get it all into a single table. will take some time, but doable.

Jimbo

15