Substract between two different tables

Hi there,

I’m relatively new to Knime and I am trying to achieve something that I’m sure is quite simple but I can’t figure it out. I know several topics have already discussed this but I can’t quite make it work.

Here is my data.

You can see in the table the number of incoming clients

In the next you can see the number of churning clients.

I need to know the resulting substraction between the two tables. I.e.:

Year Month Julio Agosto Septiembre
2019 7 4285-86 (4285-86)-24 (4285-86-24)-13
2019 8 ? 4115-108 (4115-108)-22
2019 9 ? ? 4782-135

Thank you in advance!

Hola @jorgemartcaam,

Could you please upload a workflow with the two tables so that I can provide you with a solution ?

Best regards,

Ael

Sure! Here you have it.

example_forum_substraction.knwf (7.1 KB)

Gracias! :blush:

Hola @jorgemartcaam

Please find attached the solution to your problem:

20200331_PIKAIROS_SUBSTRACT_BETWEEN_TWO_DIFFERENT_TABLES.knwf (147.2 KB)

The solution is involved because you need to do a cumulative sum accross columns and this requires in KNIME a table transposition, before table substraction. I have also dealt with the case of having to substract two tables with different column names as it is the case in your example :

Hope this is of help :wink: !

Saludos

Ael

4 Likes

Thanks! It works like a charm.
Only one question, though. It will update automatically when new years and months are written on the tables, right?

1 Like

It should. I made it to be as generic as possible, so the only rules you need to follow are :

  • The two tables should be of same size,
  • The two tables should be organized in the same way (same type of columns and rows in the same order),
  • The two first columns should always exist, whatever their name (here Year & Month),
  • The remaining columns should be numeric (int type),
  • Names of table columns can differ between tables,
  • The “incoming” table column header is the reference to appear in the resulting table.

Please let me know otherwise :wink:

Suerte y exito !

Ael

1 Like

I like how you are doing the substraction with the groupby node! Nice idea!

2 Likes

Thanks @tbtt for your nice comment :blush:
There are a few unusual tricks b.t.w. in this workflow. I found the problem chalenging and hence interesting.

And thanks a lot for clicking on the heart :heart_eyes: !

2 Likes

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