Substract between two different tables

KNIME Analytics Platform
1

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

image
image932×227 6.75 KB

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

image
image937×217 6.59 KB

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!

2

Hola @jorgemartcaam,

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

Best regards,

Ael

3

Sure! Here you have it.

example_forum_substraction.knwf (7.1 KB)

Gracias! :blush:

4

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 :

20200331_PIKAIROS_SUBSTRACT_BETWEEN_TWO_DIFFERENT_TABLES
20200331_PIKAIROS_SUBSTRACT_BETWEEN_TWO_DIFFERENT_TABLES1718×403 46.2 KB

Hope this is of help :wink: !

Saludos

Ael

5

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?

6

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

7

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

8

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: !