Merge Tables with different number of rows.

KNIME Analytics Platform
1

Hi, I´m a new user for Knime.

First at all, I excuse for my english, I speak spanish. I have the same problem that other users. How can I merge two tables where each one has a different number of rows? Here is an example of what I try to do:

TABLE 1
Brand Qty Month
Toyota 1 Jan
Chevrolet 3 Jan
Chevrolet 4 Jan
Hyundai 4 Jan
Renault 4 Jan
Renault 1 Feb
Mazda 1 Mar
Renault 8 Mar
Renault 9 Mar
Renault 0 Apr
Renault 6 Apr
Hyundai 5 Apr
Chevrolet 4 Apr
Chevrolet 2 May
Chevrolet 1 May
Chevrolet 1 May
BMW 2 May
BMW 3 Jun

TABLE 2
Brand Sold by
Chevrolet John
BMW Mack
Hyundai Peter
Mazda Ross
Renault Judy
Toyota Caroline

I need to merge this two tables to have in the TABLE 1 the person who sold the vehicle. Then, the result that I need is:

Brand Qty Month Sold by
Toyota 1 Jan Caroline
Chevrolet 3 Jan John
Chevrolet 4 Jan John
Hyundai 4 Jan Peter
Renault 4 Jan Judy
Renault 1 Feb Judy
Mazda 1 Mar Ross
Renault 8 Mar Judy
Renault 9 Mar Judy
Renault 0 Apr Judy
Renault 6 Apr Judy
Hyundai 5 Apr Peter
Chevrolet 4 Apr John
Chevrolet 2 May John
Chevrolet 1 May John
Chevrolet 1 May John
BMW 2 May Mack
BMW 3 Jun Mack

I saw other post about similar topic, but I can´t find the way to do that. I try to use de Joiner combined with the RowID but it does not work. Thats because in the main table (TABLE 1), the column that I use to join is Brand and this column has some items with the same value (i.e. Renault).

How can i do this?

Thanks in advance for your help,

Luis

2

Hi Luis, Please check out this post… Regards, Thomas

3

Hi Gabriel,

Thanks. The post was very useful. It took me a lot of time, but in the end, i finally got it. I have another question: Wher I can find the functions to data management. I need to work with a database and i need to do few calculates with the data, like:

  • I have a column with the format dd/mmm/yyyy. How I can extract the year?
  • I need to do calculates with the data located in the row before I have. Here is what i need:

A 1
B 2
C 2

If Row3Col2 (2) = to Row2Col2 then “Duplicate” else “Unique”

Can you helpme?

Regards.

Luis

4

Hi Luis,
There are different categories in KNIME, for example ‘Databases’ contains a set of nodes in order to connect to different databases. The Time Series package becomes handy when working with data and time fields, here you find nodes to Extract Data/Time values. For logical row-based operation, I personally prefer the Java Snippet node, but the Rule Engine might also a node to have a look at.
Best, Thomas

5

Hi Thomas,

Can you send me your solution to merge tables with different numbers of rows? I cannt open the post link.

Thanks

Shu

6

Hi Shu,
your rever to an old post. For this sample i would use the joiner node.
But if you have a special case i recommend to create a new post with your specific problem. In addition it is allways helpful to attach sample data as far as this is possible.

BR

2 Likes