Comparing 2 xlsx files

[carlos_LOGO]

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

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

Any advice?

Thanks

[Daniel_Weikert]

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
br

[carlos_LOGO]

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

[carlos_LOGO]

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.

[Daniel_Weikert]

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

[carlos_LOGO]

Thanks again @Daniel_Weikert , much appreciated

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

Br

[gonhaddock]

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.

How does KNIME handle processing a CSV file in a workflow? Does it recognize old information and optimize the loading to process only new information?

Hello @bremels I am re-reading your post, and maybe my workflow may not be helpful; in the case that you are working with a re-written file, then it is not the same case than the provided workflow, however some techniques can be used in the same way (hashing / outer join / compare). If the case (csv without uid column) I would reconsider your data acquisition processes. So more than KNIME intelligently can recognize differences, it is more about the processing that you want to apply, and how …

Let me know about any further clarifications if needed.

BR

[carlos_LOGO]

thanks @gonhaddock