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

Learning Hub Academy Feedback
1

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!!

2

@Karan28
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

3

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

4

@Karan28
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
5

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

6

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