Comparing 2 xlsx files

How can I easily improve the comparison between 2 xlsx files (or 1 xlsx with 1 csv) considering that it is a recurring task where data is updated everyday.

File 1 = Authorized transactions
File 2 = Invoiced transactions

Problem is that data is not always presented in the same way :face_with_raised_eyebrow:

I would like to identify quickly:
1/ the transactions that are OK
2/ missing transactions
3/ incorrect transactions

Any advice?

Thanks :slightly_smiling_face:

use a groupby node to sum up the prices and then join the data and check whether difference is 0 (e.g. math formula node)
if it is โ†’ works
if it is not โ†’ not OK
if there is no join mathc โ†’ missing


Thank you @Daniel_Weikert , I 'll try to do it with my newbie knowledge :smiley:

Thankas again for the tip. Solution seems to be promising but I am not sure that every single data is included in the results. For the time being I am under the impression that only full matching rows, and partial matching rows are identified properly.
The rows without match in both files are not identified. I am probably doing something wrong.

That depends on your join type. If you include the left unmatched and right unmatched rows (see joiner config) then you get all

1 Like

Thanks again @Daniel_Weikert , much appreciated :slight_smile:

1/ Indeed, at the moment, the joining type is as follows:

2/ I did try different joining types, but the results are the same:

3/ When selecting only the left and right unmatched rows, no results are retrieved:

Considering the original number of entries in input files(1249 vs 781), even when grouped (631 vs 599), under joining type 1/ and 2/ I get only 541 entries of full matching or partial matching, It seems as I am losing unmatched rows at some point in my output file


Hello @carlos_LOGO , and welcome to the KNIME community.

Please take a look to the following post solution; this is for a similar use case to the one in your challenge.

Let me know about any further clarifications if needed.



thanks @gonhaddock :wink:

1 Like

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