Joining with multiple to one relationships

I am doing a join by concatenating two fields. I am comparing two tables. In one table I have a single line entry. In table 2 from a different source I have the same entry, then a refund and reissue. The refund and re-issue has not updated in the source that provides the data for Table1 The join, of course, matches on both the original entry and the re-issue thereby adding matched lines on a full join. Anyone have ideas how to avoid this?
uiPathJoinexample.docx (23.5 KB)

there are several options to rule the join pairing differently. However it would be the best when you share the excptect outupt.

In case of the redundand rows are to avoid then maybe remove duplicates from join result or dt2 could be an option

Cant share the file but I want a full join - top of the result showing matches and then showing what is in Table 1 but not in Table 2 and then what is in Table 2 but not in Table 1. This is for a reconciliation - I cannot remove duplicates because then I lose the re-issue of the return in Table 2. So in essence, the join I have input Dt1 and Input Dt2 to result in Dt3 with a full join. Even if I do an inner or left join the bot is still adding a second value because it is finding it twice in Dt2

Maybe a join is not the way but even if I did a for each loop match, it will still find it twice in Dt2 and there are over 200,000 lines in each Dt

sure we respect your privacy constraints. But with sample data in a form like

Col1, Col3
A1, C1

we can use and describe cases

this is a string hint for a outer join

Keep in mind the nature working mode of a join. From the sample data from above

dt1:A1,B1 join on Col1 with dt2 will produce following:
taking first A1 from dt1:

  • joins on dt2: A1,C1
  • joins on dt2: A1,C2

second A1 from dt1

  • joins on dt2: A1,C1
  • joins on dt2: A1,C2

So 4 join result rows will be paired

If e.g. join datatable activity (outer join ) will not produce the expected results we can do:

working with dataset extensions interssect and except

we can go for customized LINQ statement

we can work on filtering logic done with LINQ implementing: loop over dt1 check if any corresponding row is found in dt2 or nor, let pass dt1 row the filter depending to check result
combined with the dataset approaches (except) often the needed results were recoverable

sample_dataDt2.xlsx (3.6 MB) sample_dataDt1.xlsx (1.2 MB) Please find a sample of both dt1 and dt2. Each dt has over 200000 lines with the same column headings in both.