Outer join excluding inner join

Help Something Else
1

Anyone know how to do this in uipath?
image

2

Hi @arina

You can try with join data table activity

Regards
Gokul

3

Hello @arina
Refer to this thread, you may get some idea

4

@arina

You can try with Join activity. Can you share the sample input and output

image

image

5

I get the full join but i want to exclude out the inner join.

6

i saw this video and I get the full join but i want to exclude out the inner join.

7

You can select Join Type here @arina

image

8

image
image739×308 5.73 KB

I want to get the data that’s not in both datatable based on the number column.

Here’s sample data.
Book1.xlsx (9.3 KB)

9

@arina
Some approaches e.g. LINQ, Join Datatable do not offer some Join Types out of the Box. But with other Join Types combined we can create

Outer Join excluding inner Join =
Full/Join Minus Inner Join =
Left Join excluding Inner Join Plus Right Join excluding Inner Join

So depending on the approaches we can model:

Join Datatable Approach

Build Datatable - Prepearing the empty Result DT with the tagert structure: Number, Amount1, Amount2 - dtResult

Join DataTable | Full Join - Number to Numer dt1-Left, dt2 Right
With a LINQ we do filter out the common rows and populate the dtResult2:

grafik

LINQ:

(From d In dtJoinB.AsEnumerable
Where  Not  {d("Number"),d("Number_1")}.All(Function (x)  Not String.IsNullOrEmpty(x.toString.Trim))
Let id = String.Join("", {"Number","Number_1"}.Select(Function (x) d(x).toString.Trim)).Trim
Let ra = New Object(){id, d("Amount1"),d("Amount2")}
Select r = Result2.Rows.Add(ra)).CopyToDataTable

Common rows we identify when Number AND Number_1 is not empty
ID is to pickup form Number or Number_1

LINQ Left outer Join Approach

grafik

Left Join excluding Inner Join Plus Right Join excluding Inner Join
partResult | DataType: DataTable - will hold the additions of the part step, final result we do get from the output of the preparing Build Datatable - Result3

(From d1 In dt1C.AsEnumerable
Group Join d2 In dt2C.AsEnumerable
On d1(0).toString.ToUpper.Trim Equals d2(0).toString.ToUpper.Trim Into gj = Group
Where Not gj.Any() 
Let ra = New Object(){d1(0), d1(1), Nothing}
Select r = Result3.Rows.Add(ra)).CopyToDataTable

AND

(From d1 In dt2C.AsEnumerable
Group Join d2 In dt1C.AsEnumerable
On d1(0).toString.ToUpper.Trim Equals d2(0).toString.ToUpper.Trim Into gj = Group
Where Not gj.Any() 
Let ra = New Object(){d1(0),Nothing, d1(1)}
Select r = Result3.Rows.Add(ra)).CopyToDataTable

Kindly note: CopyToDataTable is used for getting the part and inspection purpose

Set operations like Intersect / Except we can not use as the match is to be done on 1 column.

Group By Approach

as dt1, dt2 does not have duplicated ID numbers we can use a group by approach (can be extended to handle also duplicated ID per Side, if needed)

grafik

we merged dt2 into dt1

LINQ:

(From d In dt1A.AsEnumerable
Group d By k=d("Number").toString.Trim Into grp=Group
Where grp.Count = 1
Select r = grp.First()).CopyToDataTable

Big credit to one of my colleagues @kumar.varun2 working with me on this and also can provide a 4th approach

2 Likes
10

This is a wonderful approach. I do think on linq but my knowledge is limited. Can you suggest where I can learn linq?

11

have a look here:

12

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