Substract between two different tables

[jorgemartcaam]

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!

[aworker]

Hola @jorgemartcaam,

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

Best regards,

Ael

[jorgemartcaam]

Sure! Here you have it.

example_forum_substraction.knwf (7.1 KB)

Gracias!

[aworker]

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 !

Saludos

Ael

[jorgemartcaam]

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?

[aworker]

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

Suerte y exito !

Ael

[tbtt]

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

[aworker]

Thanks @tbtt for your nice comment
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 !