Please, advise me with which functions and / or activities (or combination) are best to use to compare two datatables in which the values may be equal or partially equal? Thus, in the best case, it is necessary to obtain a table with joined “ideal” matches, “partial” matches and separately data that could not be compared in any way, by certain columns.
Earlier, I used the Join DataTable activity, but it allows you to connect only perfect matches by key fields, and this partial once are end up outside of joined datatable.
I’m very new in UiPath, but because of certain deadline I’ve decided to get some help here.
@Shony1993 Can you provide us with some sample input data, and explain the operation or the conditions that you want to apply on that and then maybe an Expected Output of that data, would be helpful and it might increase the speed to Solve the problem.
Sorry, I can’t attach files because of my newly status. So I add DataTables in message below.
First DataTable is Sample from Account transactions in this format:
Date
Document
Counterparty
Amount
14.01.2020
Payment
Name 1
11 063
21.01.2020
Payment
Name 2
10 231
13.02.2020
Payment
Name 3
5 000
24.01.2020
Payment
Name 3
11 012
Second DataTable is result of my other robot that reads pdf payment orders in following datatable:
Date
Counterparty
Amount
Number of document
14.01.2020
Name 1 + some information
11 119
23184
13.02.2020
AO “Name 3”
5 000
2677
24.01.2020
AO “Name 3”
11 012
1325
23.03.2020
Name 4
10 500
15
Resulting datatable must match three lines from tables (Name 1 and two Name 3), although one of amounts (for Name 1) is slightly different in this two tables. One line in first and one line in second mismatched to any other lines in tables, they off board of table, so user can see them and analyse himself manually.
I need to match lines with all possible accuracy. As you can see in second table name of the counterparty may be with additional word or quotes or some other differences. Amount can be different also, due to mistakes in account, or this amount has two or more transactions, which are not in Sample table. Date also may be different sometimes. Pdf order, which we get from outside, may be wrong, not from our Sample of transactions. But alas, I need to match them any possible way, and if they don’t match either way, I need to show it to end user who will check it manually.
I don’t think that I must seek perfection here, only the most effective way. If some of lines mismatched, then it must be left for manual checking.
Therefore, I ask for advice on how this can be implemented (with which instruments and functions) and whether it is even possible.