Left Outer join Query - UiPath

Hi All,

I have 2 excel files Excel1 and Excel 2. I have connected with those file as a database and store the data into 2 data tables (dtData1, dtData2)
The intension is that bot should take all the data from the left table and and only the matching data form the right table.

I have created another data table(dtResults) so that query can add the data into that table. I have written the query but only 1 column is coming from right side. but i need 3 columns from right table.

Here is the query I have use

(From d1 In dtData1.AsEnumerable
Group Join d2 In dtData2.AsEnumerable On d1(0) Equals d2(0) Into gj = Group
From g In gj.DefaultIfEmpty
Select ra = {d1(0), d1(1) ,If(isNothing(g), Nothing, g(1)) }
Select dtResults.Rows.Add(ra)).CopyToDataTable

any idea to solve the issue ?

Regards,
Niladri

Here IT IS only defined for one from right Side Table. Just extend IT Catch also the Others 2 cols

Thanks Peter for your reply when I am applying that it is not working. I was trying with the below also but not working as if takes one input for false. where it is false it should give the remining columns

(From d1 In dtData1.AsEnumerable
Group Join d2 In dtData2.AsEnumerable On d1(0) Equals d2(0) Into gj = Group
From g In gj.DefaultIfEmpty
Select ra = {d1(0), d1(1) ,If(isNothing(g), Nothing,g(1).g(2)) }
Select dtResults.Rows.Add(ra)).CopyToDataTable

Can you please guide?

Like this?

Select ra = {d1(0), d1(1) ,If(isNothing(g), Nothing,g(1)),If(isNothing(g), Nothing,g(2)),If(isNothing(g), Nothing,g(3)) }

But @ppr has usually a better solution :slight_smile:

Cheers

assumption dtresults is prepared and within the right column structure

Give a try on

(From d1 In dtData1.AsEnumerable
Group Join d2 In dtData2.AsEnumerable On d1(0) Equals d2(0) Into gj = Group
From g In gj.DefaultIfEmpty
Let chk = isNothing(g)
Let ra2 = If(chk, new Object(){nothing,nothing,nothing}, new Object(){nothing, g(1), g(2)})
Let ra1 = new Object(){d1(0), d1(1)}
Let ra = ra1.Concat(ra2).toArray
Select dtResults.Rows.Add(ra)).CopyToDataTable

You can use the Join Data Table activity and specify that you want to do a left join. Hope that’s what you are looking for.
ELW0~ISUXD~BVO3{RKGHEM

Thanks

But as data is huge, job is getting stopped

Regards,
Niladri