Concatenate Values from two tables without joining

Table1 includes a row where Row ID = Row0_180. And a column named Pets contains “Dog”

I want to create a new Column that equals the contents of Pets, concatenated with contents of the Pets column from Table2, where Row ID is one less (i.e. Row0_179) which contains “Cat”. So the new column would be “Dog Cat”

My full case would include concatenating multiple cells from Table2. Table 2 is huge, so a join could be difficult.

I’m guessing some kind of string manipulation e.g. joinSep(" ", [Table1/Pets/RowID], [Table2/Pets/RowID-1], etc.)

So how do I grab values from another table based on decrementing the value of RowID?

Cheers, Richard

Hi @RIchardC

Did you check the Column Appender node. If both tables have the same length and are sorted in the right order, than RowID doesn’t matter anymore.
gr, Hans

I would recommend to tke look on this joiner

node.

2 Likes

I should have said, Table 1 has about 20 rows and Table 2 has 140,000 rows. I don’t think joining is practical.

Is there anything that will let me grab cell contents from Table2 based on RowID?

I think joining is inevitable. Besides that, 140k lines is peanutes for KNIME.

Created a dummy setup with 140k random numbers, tried to join one random row ID somewhere, the joiner is executed instantaneously. Practically, it took 38 milliseconds to execute so you shouldn’t be afraid of that :wink:

image

2 Likes

Thanks to all. Glad to know that join is inevitable.

So I have everything in one table. How can I create a new columnB that concatenates:
ColumnA from the previous row
ColumnA from the current row
ColumnA from the next row

In the example below, in the row where columnA shows nerve, the new ColumnB would show
the nerve to

image

If it makes a difference, I actually want to concatenate the previous 20 rows and next 20 rows.

That looks like a perfect job for the lag node All you need is ... the Lag Column Node! | KNIME

1 Like

Other option would be to add a common identifier for your 40 rows and then use a group by node with concatenate
br

1 Like

Excellent. Thank you. I marked the Lag Column as the solution, although many other posts were also necessary. Thanks.

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