Full Join - compare datatable 1 with datatable 2 - return matching and not matching items

Hi All,

Datatable1
Column A Column B
Karan 28

Rahul 29

Ashu 30

Datatable2

ColumnA ColumnB column C
Ashu 20
Charan 30
Kimble 40

I have read both the datatable using read range now in dt1 and dt2.
Now I need to check if row item of dt1 means dt1.row(0) present in dt2 column A or not.
if it present present then I have to copy the value of ColumnB from dt 1 to dt2 for that row.

If dt2.row(0) is not present in column A of dt2 then I have to add that datarow means column A and Column B of dt1 to dt2.

Please help

The most simple solution to this problem would be to use two for each row activities, one inside another.
The first one would iterate over d1 table. At the beginning of it set a flag (boolean variable) nameFound to FALSE. Then you can place second For Each Row activity.
Inside it compare if values from both rows are the same. If they are, assign values from d1 to d2 and set nameFound flag to TRUE. Then you can break the loop using break acitivity, if records are unique and you don’t need to check the rest of them. If records are not unique you can iterate over rest of the records.
After you will step out of internal loop set an IF block checking the nameFound flag. If name was not found, then you can add it to the second datatable there.

Of course this is not the cleanest solution, “it’s a simple spell, but quite unbreakable”. I encourage you to try doing that with LINQ, as you would probably be able to do this in a single line of code.

I will try this one now , in the meantime could you please help me with the LINQ query for the same?

how to compare the row item of dt1 and dt2?

@Mariusz
first stop activitiy would be Join Datatatable configured as FUL Join type.
It is suggested to start ant to explore this if this will do the job.

For more custom requirement then a LINQ statement can be written.

I have tried today the Join Datatable using full join but its not working that way.

Could you help me with the linq query?

@Karan28
Sure we will help you. Just support us on this with detail input sample data including column structure and clear defined expected output.
Thanks

Yes
,Please go through once again with my question at the start

Just support us on this

Do you want me to send you a excel for that?
In that I can explain with the help of sample data

CheckDuplicates.xlsx (11.8 KB)

Hi I have explained issue in the attached excel file .Please refer to this

@Karan28
the Join activity did a good job for doing the full Join
grafik

Linq was helpfully for bring it into the target structure:

(From dj In dtJoin.AsEnumerable
Let c1 = If(String.IsNullOrWhiteSpace(dj("Column1").toString), dj("Column1_1"),dj("Column1"))
Order By c1
Let ra= New Object(){c1,dj("Column2"), dj("Column2_1")}
Select dtResult.Rows.Add(ra)).CopyToDataTable

grafik

Find demo XAML here:
FullJoin_TotalColResult_JoinActivity.xaml (12.2 KB)

1 Like

Hey , Thankyou so much with this amazing solution

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