Compare datatables using linq

Help Studio
, ,
1

Hello there!

I’ve facing some issue while comparing two datatables using linq.
I want to update a comment based on comparison and don’t want any row to be eliminated(as in intersect operation)

Sample DT1:

Process Created By CreationTime ModifiedBy Modification Time Deleted By DeletionTime Comments
A Andrew 9/25/21 9:21:00
B Jane 9/27/21 9:31:00

Sample DT2:

Process Created By CreationTime ModifiedBy Modification Time Deleted By DeletionTime Comments
A Andrew 9/25/21 9:21:00 Jane 9/29/21 9:21:00
B Jane 9/27/21 9:31:00 Jack 9/30/21 9:21:00
C Lee 9/30/21 9:31:00

Expected output:

Process Created By CreationTime ModifiedBy Modification Time Deleted By DeletionTime Comments
A Andrew 9/25/21 9:21:00 Jane 9/29/21 9:21:00 Modified
B Jane 9/27/21 9:31:00 Jack 9/30/21 9:21:00 Deleted
X Rose 9/20/21 9:31:00
C Lee 9/30/21 9:31:00 Added

Any leads would be highly appreciated!

Cheers,
Abhi

2

Sample DT1:

Process Created By CreationTime ModifiedBy Modification Time Deleted By DeletionTime Comments
A Andrew 9/25/21 9:21:00
B Jane 9/27/21 9:31:00
X Rose 9/20/21 9:31:00
3

@abhi2509
your case looks similar to the following that we solved:

4

Hi @abhi2509

You try the below three queries

image
image1026×429 11.4 KB

Query 1 - to add modified rows -
((From i In dt_Datatable_1 where (from l in dt_Datatable_2 where i(“Process”).tostring=l(“Process”).tostring and l(“Modified by”).tostring<>“” select l ).count>0 select dt_Output.Rows.add({i(“Process”).tostring,i(“created by”).tostring,i(“creation time”).tostring,((from l in dt_Datatable_2 where i(“Process”).tostring=l(“Process”).tostring and l(“Modified by”).tostring<>“” select l ).first)(“Modified by”).tostring,((from l in dt_Datatable_2 where i(“Process”).tostring=l(“Process”).tostring and l(“Modified by”).tostring<>“” select l ).first)(“Modified Time”).tostring,i(“Deleted by”).tostring,i(“Deletion Time”),“Modified”}))).copytodatatable

Query2 - to add deleted rows -
((From i In dt_Datatable_1 where (from l in dt_Datatable_2 where i(“Process”).tostring=l(“Process”).tostring and l(“Modified by”).tostring<>“” select l ).count>0 select dt_Output.Rows.add({i(“Process”).tostring,i(“created by”).tostring,i(“creation time”).tostring,i(“Modified by”).tostring, i(“Modified Time”).tostring,((from l in dt_Datatable_2 where i(“Process”).tostring=l(“Process”).tostring and l(“Deleted by”).tostring<>“” select l ).first)(“Deleted by”).tostring,((from l in dt_Datatable_2 where i(“Process”).tostring=l(“Process”).tostring and l(“Deleted by”).tostring<>“” select l ).first)(“Deletion Time”),“Deleted”}))).copytodatatable

Query3 - to add Added rows -
((From i In dt_Datatable_2 where (from l in dt_Datatable_1 where i(“Process”).tostring=l(“Process”).tostring and l(“Modified by”).tostring<>“” select l ).count=0 select dt_Output.Rows.add({i(“Process”).tostring,i(“created by”).tostring,i(“creation time”).tostring,i(“Modified by”).tostring, i(“Modified Time”).tostring,i(“Deleted by”).tostring,i(“Deletion Time”),“Added”}))).copytodatatable

5

Hi @keerthi3595

I’m getting below error after trying the first query:

Expression of type ‘Integer’ is not queryable. Make sure you are not missing an assembly reference and/or namespace import for the LINQ provider.

Is it possible for you to attach the working seq here?

Thanks!

Cheers,
Abhi

6

Hi @ppr

The link which you’ve attached is just for comparison whereas in my case I’ve to compare and add appropriate comments for it.

Thanks!

Cheers,
Abhi

9

Find some starter help here:
ppr_LINQBox_CustomMerger_Abhibha2509.xaml (18.6 KB)

10

Please find the attached xaml file
Sequence1.xaml (16.3 KB)