[LINQ] Return of values from and matching two different data tables

[anthonyjr (Anthony)]

Good day LINQ masters,

I want to join two different data tables with matching the columns UIDa(dt_Driver) from UIDb(dt_Track).
For unmatched rows, I want to put #N/A on columns from dt_Track(excluding UIDb). I’m not really good on making context but I hope with this input and output it will explain the rest.

This is the process and linq that I tried but it seems it does not return the values from dt_Track
Test.xaml (13.7 KB)

(
	From part In dt_Driver
	Group Join trans In dt_Track
	On trans("UIDb").ToString Equals part("UIDa").ToString.Replace("'","") Into grp = Group
	From g In grp.DefaultIfEmpty
	Let UID = g("UIDb").ToString
	Let Name = part("Name").ToString
	Let Car = part("Car").ToString
	Let Track = If(IsNothing(g), "#N/A",part("Track").ToSTring)
	Let Country = If(IsNothing(g), "#N/A",part("Country").ToString)
	Let Remarks = If(IsNothing(g), "#N/A", "WINNER")
	Select dt_Consolidated.Rows.Add(UID,Name,Car,Track,Country,Remarks)
).CopyToDatatable

[Rahul_Unnikrishnan]

Hello @anthonyjr

Is this a requirement for uipath automation, then can you use Join activity? Or is there any restriction to use that?

[anthonyjr (Anthony)]

Good day sir @Rahul_Unnikrishnan

We tried using Join Activity of UiPath at some other time, the problem with Join activity is that when using it on more than one million transactions it causes Job Stop on the execution because of UiPath memory capacity. This is the reason why we are trying to implement in LINQ to avoid the stopping of the execution.

[anthonyjr (Anthony)]

It seems that I already got the solution but there is a ghost row where I don’t where it come from.

(
	From part In dt_Driver
	Group Join trans In dt_Track
	On part("UIDa").ToString Equals trans("UIDb").ToString  Into grp = Group
	From g In grp.DefaultIfEmpty
	Select dt_Consolidated.Rows.Add({If(IsNothing(g), "#N/A",g("UIDb")),part("Name"),part("Car"),If(IsNothing(g), "#N/A",g("Track")),If(IsNothing(g), "#N/A",g("Country")),If(IsNothing(g), "#N/A", "WINNER")})
).CopyToDatatable

Test.xaml (18.6 KB)

[ashwin.ashok ((TheCodingTheory))]

Hi @anthonyjr ,

Seems like you were able to find the solution on your own, congrats!

As for the ghost row, you might want to check if the Input DataTables contain empty rows in the Immediate Panel.

Try fetching its row count and see if the input tables contain more than 2 rows.

Kind Regards,
Ashwin A.K

[anthonyjr (Anthony)]

Thank you sir @ashwin.ashok , I logged the number of rows of each input data tables, it seems that I miss out a blank row on the first data table after the header.