Join Two DataTables

[samir (Samir Mohite)]

hi @krisleunis,
You can compare two dataTables and get extras or I can say unmatched records by using this:

dt3 = dt2.AsEnumerable().Where(function(f) Not dt1.AsEnumerable().Select(function(i) i.Field(Of Int32)(“ID”).ToString).Any(function(i2) i2 = f.Field(Of Int32)(“ID”).ToString)).CopyToDataTable()

e.g.
dt1 → has 3 records,
dt2 → has 4 records (1 extra).

here’s my workflow:
Main.xaml (9.6 KB)

I hope this will help you.

[krisleunis]

Hi @samir,

Many thanks for your help.
Your workflow indeed works for the example I provided.
However, it does not work in the scenario where both datatables contain unique records.

Please find the example below:

dtInputOne
ID,Value
1,“John”
6,“Albert”
2,“Ringo”
3,“Paul”
4,“George”

dtInputTwo
ID,Value
1,“John”
2,“Ringo”
3,“Paul”
4,“George”
5,“Yoko”

Based on these, how can I obtain?

dtOutput
ID,Value
6,“Albert”
5,“Yoko”

P.S. This is a feature I expected the Join Data Tables activity to have the JoinType “Outer”

[RohitSuperCool009 (Rohit Shankar Lanjewar)]

Hello vvaidya!! Thanks for your solution. I need to ask how to join 2 different sheets of Single Excel Workbook into single excel sheet. I want to merge two excel sheet into one sheet. I have excel workbook SampleClientsData.xlsx. It contains 2 different sheets first is “Client_Members_Sheet” and another is “Sales_Member_Sheet”. I want to get Sales_Member_Sheet data into Clients_Members_Sheet without blank columns and proper data. I tried using Merge Datatable. But it is not working. It is adding extra 3 blank columns into excel sheet “Clients_Members_Sheet”.
I am sending you the flow and Screenshots and Excel sheet. Please suggest me proper solution to this problem.

MergingTwoExistingDatable.xaml (7.9 KB)
SampleClientsData.xlsx (11.9 KB)

PFA
Regards
Rohit Lanjewar

[RohitSuperCool009 (Rohit Shankar Lanjewar)]

Hello Aksh!! Thanks for your solution. I need to ask how to join 2 different sheets of Single Excel Workbook into single excel sheet. I want to merge two excel sheet into one sheet. I have excel workbook SampleClientsData.xlsx. It contains 2 different sheets first is “Client_Members_Sheet” and another is “Sales_Member_Sheet”. I want to get Sales_Member_Sheet data into Clients_Members_Sheet without blank columns and proper data. I tried using Merge Datatable. But it is not working. It is adding extra 3 blank columns into excel sheet “Clients_Members_Sheet”.
I am sending you the flow and Screenshots and Excel sheet. Please suggest me proper solution to this problem.

MergingTwoExistingDatable.xaml (7.9 KB)
SampleClientsData.xlsx (11.9 KB)

PFA
Regards
Rohit Lanjewar

[RohitSuperCool009 (Rohit Shankar Lanjewar)]

Hello Samir!! Thanks for your solution. I need to ask how to join 2 different sheets of Single Excel Workbook into single excel sheet. I want to merge two excel sheet into one sheet. I have excel workbook SampleClientsData.xlsx. It contains 2 different sheets first is “Client_Members_Sheet” and another is “Sales_Member_Sheet”. I want to get Sales_Member_Sheet data into Clients_Members_Sheet without blank columns and proper data. I tried using Merge Datatable. But it is not working. It is adding extra 3 blank columns into excel sheet “Clients_Members_Sheet”.
I am sending you the flow and Screenshots and Excel sheet. Please suggest me proper solution to this problem.

MergingTwoExistingDatable.xaml (7.9 KB)
SampleClientsData.xlsx (11.9 KB)

PFA
Regards
Rohit Lanjewar

[CristianZachiteanu (Cristian Nicolae Zachiteanu)]

Hi,

If you are still looking for a solution to write selects like in SQL please see the example below in LINQ.

(From s In DTConsolidatedWithIntervals.AsEnumerable()
Join r In DTDatesWithIntervals.AsEnumerable()
On s(“Station ID”).tostring Equals r(“Station ID”).tostring
Where (s(“Date Start”).tostring >= r(“Date_From”).tostring And s(“Date Start”).tostring <=r(“Date_To”).tostring) Or (s(“Date Start”).tostring<r(“Date_To”).tostring And s(“Date End”).tostring>r(“Date_To”).tostring) Or (s(“Date End”).tostring>=r(“Date_From”).tostring And s(“Date End”).tostring<=r(“Date_To”).tostring)
Select DTCentralised.LoadDataRow(New Object() {r(“Date”),s(“User ID”),s(“Car registration”),s(“Station ID”),s(“Date Start”),s(“Date End”),r(“Date_From”),r(“Date_To”),If(r(“Valid From”).tostring=“”,Nothing,r(“Valid From”)),If(r(“Valid To”).tostring=“”,Nothing,r(“Valid To”).tostring),r(“Price per hour (RON)”),r(“Discount per hour (RON)”)},False)).tolist()

I don’t have an example with outer but I think the LINQ has this functionality.

Happy Development
CristianZ