Yeah i have mentioned it in the post already if you have different then you can use For-each construct or For-each row activity and add new data to a new datatable using Add Data row activity.
You means if suppose same email field found in both then just skip that row or if not present then just add it ? right?
Having joined 2 tables, I now have some rows with blank fields (because the join field is only present in 1 table). I have tried to filter and remove rows based on the blank fields but this is not working. How can the output table best be filtered to include rows for which join data is available in both tables?
I’m working on something similar and I cannot seem to get this to work. I’ve tried both the default Merge DataTable activity and your Invoke Method version, and I am having the same issue in both cases - the rows from the second datatable are being appended rather than merged with the rows with the same primary key from the first datatable.
In my case, the datatables in question are being generated by the Excel Read Range activity to pull from two different worksheets, but I have confirmed that the Excel formatting is the same, and the columns are successfully merging where applicable, just not the rows with the same primary key.
When i declare the PrimaryKey; and so the key has already repeated values on the column. How can i previously detect and delete them (with a rule like keeping the first or the more fullfilled row) (I was planning a double for to check everything, but to hard with plenty of rows)
I whant to do a LEFT join, so if the 2nd Datatable has extra values of keys that doesn’t appear on the 1st DT, don’t include that rows on (i was planning to iterate and clean those, but maybe you have a better efficient idea. Also a DT.Select could be)
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.
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.
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.
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.
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.