DT Comparison using Outer Join Query

Help Studio
1

Hi Team,

I have 2 datatables DtA and DtB and want to fetch the records exists in DtA and not in DtB. I have more than 30000 records in both the datatables. I used linq except query to fetch the records but it is finding case sensitive records also as non match. Could anyone help me which join in uipath can be used?

Thanks,
Ula

2

@Boopathi Can you try this and Check :
In_DataTable1.AsEnumerable().Where(function(row) Not In_DataTable2.AsEnumerable().Select(function(r) r(“yourColumnName”).ToString.Trim.ToLower).Any(function(x) x = row(“yourColumnName”).ToString.Trim.ToLower).CopyToDataTable()

3

Did we try with JOIN DATATABLE ACTIVITY with left join method

@Boopathi

4

Hi @Palaniyappan Thank you. Left join returns all the data from the DTA and matched records in DTb correct then how it can return only exists in DTa and not in DTb. Left join not returning expected data

1 Like
5

Hi @supermanPunch Thank you. I am trying your query. Could you please clarify the function syntax as it displaying Select(function r(“yourcolumnname”)

6

@Boopathi It Should be something Like this : Function(x) x(“yourColumnName”) instead of x use r

I guess it Comes as a Registered mark if i use it here :sweat_smile:

7

Thank you. Also can i get the expected result by comparing one column name between 2 datatables.

IDnumber is commmon to both the tables and want record exists in dta and not in dtb using this id number as reference.

8

Fine
Once after getting the output datatable from JOIN DATATABLE activity with a variable named Finaldt

Then use a assign activity like this
DtA = DtA.AsEnumerable.Except(Finaldt.AsEnumerable,DataRowComparer.Default).CopyToDatatable()

This will give us the required output
Cheers @Boopathi

1 Like
9

Hi @Palaniyappan

Thank you. I am able to get the desired output using an another linq query but now new error is coming as unable to case system.double to system.string. Could you please check and given below the code

DT1.AsEnumerable().Where(function(row) Not DT2.AsEnumerable().Select(function(r) r.Field(Of String)(“ID Number”)).Any(function(x) x = row.Field(Of String)(“ID Number”))).CopyToDataTable()

10

Fine
Make sure the column type is string or if not use .ToString along the column here in this expression

And
I hope this expression would help you [quote=“Palaniyappan, post:8, topic:195801”]
DtA = DtA.AsEnumerable.Except(Finaldt.AsEnumerable,DataRowComparer.Default).CopyToDatatable()
[/quote]

Cheers @Boopathi

11

@Boopathi Us that Linq Query in this way :

DT1.AsEnumerable().Where(function(row) Not DT2.AsEnumerable().Select(function® r(“ID Number”).ToString).Any(function(x) x = row(“ID Number”).ToString)).CopyToDataTable()

12

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