Appending columns to existing table based on keys

Hello fellow KNIMErs,

I have multiple tables which contain route IDs and the number of times they have been used:

Table for week 41:

Route ID Times of use
2792 8
2852 18
6092 10

Table for week 42:

Route name Times of use
2792 23
2852 22
5091 8
6611 5

I also have a reference table with all possible routes that I want to use to match the counters of any week to and obtain a result like this:

Route ID reference week 41 week 42 Difference
2792 8 23 15
2852 18 22 4
5121 0 0 0
6092 10 0 -10
6093 0 0 0
7511 0 0 0
6611 0 5 5
5041 0 0 0
5091 0 8 8

Ideally, this table will grow by 1 new column each week and the current difference would be re-calculated on the latest 2 weeks.

Does anyone have an idea how to achieve this?

Hello @gentile

I don’t see too much complication on this, maybe I am missing something…

Just start with a ‘Column Rename’ node for the weekly table’s column $Time of use$ as in the output: $week 41$, $week 42$.

Afterwards a sequential OUTER LEFT join in two steps with your ‘Route ID reference’ table. And a ‘Math Formula’ node at the end calculating the difference.

Let us know. BR

3 Likes

For the first part, @gonhaddock is absolutely right. In regards to the additional dynamic recalculation, I would opt for a Column Expression using
column(numberOfColumns() - 3 ) - column(numberOfColumns() - 2)

Assume this small example

Week 41 and 42 are in the upper table, week 43 is joined from the lower table. After the join however, week 43 becomes the last column directly after the difference column which is somewhat inconvenient.

image

With a Column Resorter, I set the difference column to last position and any new column in front to adjust the order.

Based on this, the new week will always be in the -2 column from the right and the week before that in the -3 position. With numberOfColumns() you can get the total number of columns (5 in this case).Note that the column index starts with 0.

The column() function takes the values based on the column index, something that we have just established with the offset.

As such, applying the nested function column(numberOfColumns() - 3 ) - column(numberOfColumns() - 2) will give you the difference between week 43 and week 42. This translates to 23-10 = 10.
Note: don’t forget the replace column checkbox for the difference so that it’s updated.

See WF:
Appending columns to existing table based on keys.knwf (33.1 KB)

Hope this helps!

4 Likes

I was thinking too complicated :see_no_evil:. The joiner did it.

Wow, thank you so much! Exactly what I was looking for!

2 Likes

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