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)
@Chris_Bolin
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
dt1:
Col1,Col1
A1,B1
A2,B2
A4,B4
dt2:
Col1, Col3
A1, C1
A1,C2
A3,C2
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
- dt1-intersect-dt2=res1
- dt1-except-res1=res2
- dt2-except-res1=res3
Except - LINQ Set operator
Intersect - Set operator
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.