How to join two datatables and get duplicate and non duplicate items in column 1 from both datatables

I have to combine two datatables on the basis on column 1 and in the final datatable I want to have both common and and uncommon items in column1 of both datatables.

just like this

datatable 1 -

INR million FY18 3/31/2018
Test40 0 0
inventories at the end of the year:1 0 0
finished goods2 300.1019267 222.105
work-in-progress4 231.2314402 180.679
stock-in-trade6 30.15119893 20.776
Test58 561.4845658 423.56
inventories at the beginning of the year:9 0 0
finished goods3 222.105 283.595
work-in-progress5 180.679 258.527
stock-in-trade7 20.776 16.25
Test610 423.56 558.372
net (increase) decrease11 -137.9245658 134.812

Datatable 2

INR million FY17
Test40 0
inventories at the end of the year:1 0
finished goods2 283.595
work-in-progress4 258.527
stock-in-trade6 16.25
Test58 558.372
inventories at the beginning of the year:9 0
finished goods3 354.123
work-in-progress5 243.616
stock-in-trade7 29.629
Test610 627.368
net (increase) decrease11 68.996
XX 121
YY 131
ZZ 141

Final datatable I need after joining

INR million FY18 3/31/2018 FY17
Test40 0 0 0
inventories at the end of the year:1 0 0 0
finished goods2 300.1019267 222.105 283.595
work-in-progress4 231.2314402 180.679 258.527
stock-in-trade6 30.15119893 20.776 16.25
Test58 561.4845658 423.56 558.372
inventories at the beginning of the year:9 0 0 0
finished goods3 222.105 283.595 354.123
work-in-progress5 180.679 258.527 243.616
stock-in-trade7 20.776 16.25 29.629
Test610 423.56 558.372 627.368
net (increase) decrease11 -137.9245658 134.812 68.996
XX 121
YY 131
ZZ 141

Here the line items present in Datatable 2 which , XX, YY, ZZ are not present in datatable 1 but they are present in final datatable.

In this way I want the output , both duplicate and no duplicate items from both the datatables in the final merged datatable

Please Help!!

I saw also your other post on this scenario, but also would give the same answer. Give a try to set it up with the join datatable activity.

first table: your table 1
second table: your table 2
Join Type: FULL (this setting is very important)

The received result can be later filtered / postprocessed (e.g removing join cols etc)

the miss matching rows will be detected with Null/empty value checks:
here the join col values are compared: NR million And NR million_1
both same: match
only 1 value, other is Null or Empty - Non Matchers

For creating your final report the Join Table will be filtered / processed e.g. by a LINQ statement

Hope this helps for the start

Hi , I have asked the similar question a month back and you have given me the solution.

It would be great if you slightly modify your solution little bit as per this query I have . This is very important for my code.

please goto this topic created by me and solution given by you. Modify this solution and help me

Find some quick prototype as starter help here, based on a generic dataset
Join_1Col_MergeBoth_AddCols.xaml (11.0 KB)

Just give a play by renaming the column names as by your case.

  • check also the ColList variable, for configuring the final result list columns
  • Constraints: Join Col to keep is the first column from Join DataTable

A later more dynamizing should be possible

1 Like

Thankyou again , for this amazing solution . For now it worked perfectly :slight_smile:

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