topic Hi David, Unfortunately, I… in Product Forum

Merge Collections based on Key Field

ChrisHodgson1 — Wed, 30 Jan 2019 16:09:00 GMT

Hi, I'm looking to merge two collections with different columns that have one column with data that will match between them. Looping through both into a new collection does work, but takes far too long with collections that have 1000+ lines. Is there a way of merging them in a way similar to a SQL "Join" query? If so, does anybody have any example code? Thanks in advance, Chris

Hi Chris, Are you sure …

david.l.morris — Wed, 30 Jan 2019 23:25:00 GMT

Hi Chris, Are you sure 'Merge Collection' isn't what you need? Of course, you'd need to remove/delete the key column from one of the collections before the merge or perhaps rename it. As long as both collections have the same number of rows and exactly the same keys, then it will work just fine. You may want to do some kind of validation afterward to sanity check it. If you're saying that the columns do not have the same rows, in that one has keys that the other does not have, then what I'm suggesting isn't what you need. Respectfully, Dave

I forgot to mention you'd…

david.l.morris — Wed, 30 Jan 2019 23:28:00 GMT

I forgot to mention you'd need to sort both collections on their key columns before merging as well.

Hi David, Unfortunately, I…

ChrisHodgson1 — Thu, 31 Jan 2019 16:49:00 GMT

Hi David, Unfortunately, I am attempting to merge two collections with different columns/rows, i.e.: Table 1 ID / Name / Age AL55W / John / 21 AL89R / James / 30 AL64W / Jack / 45 Table 2 ID / Orders / Frequency AL55W / 50 / Weekly AL89R / 75 / Weekly AL64W / 100 / Monthly Both collections contain the column ""ID"", but each collection contains different data in all other columns. Effectively, I'm looking to merge these collections based on the ""ID"" column without resorting to looping through 1000+ lines on each collection as it's taking far too long. Also, sometimes Table 2 will not have the ""ID"" values from Table 1, so these would either need to be removed or returned with blank values in the joined columns. Thanks for taking the time to respond. Chris

Hi Chris, I see what you…

david.l.morris — Thu, 31 Jan 2019 18:58:00 GMT

Hi Chris, I see what you mean. I couldn't tell from the original post whether it was the same number of rows in each collection. You may be aware of this already, but I wanted to mention to completely ignore the processing time while in DEBUG mode (Process/Object Studio). It is significantly slower than in Control Room. I did a 'quick' test where I operated on 1500 rows, and it took 4 minutes in Control Room. That's not even accounting for the nested loop I imagine you'd need in your use case. Maybe LINQ is what you need in a code stage, since that's like SQL, and I imagine it's optimized for this kind of thing. Dave

Hi David, We've tried in…

ChrisHodgson1 — Thu, 31 Jan 2019 19:47:00 GMT

Hi David, We've tried in Control Room also. You're correct that it's a lot faster, but our tables are increasingly large so this seems to get slower over time. I will look into LINQ as that may help. Thanks, Chris

Hi Chris, Please give a try…

AbhishekKV — Sun, 03 Feb 2019 11:01:00 GMT

Hi Chris, Please give a try with OLEDB VBO. It should help you join the tables based on ID. Abhishek

The only issue with OLEDB is…

david.l.morris — Sun, 03 Feb 2019 21:18:00 GMT

The only issue with OLEDB is that it's expecting you to connect to a source outside of BP. I suppose it could still be faster to write both tables to Excel and then query them both to join on ID. But at that point, why not just get someone to write a reusable action using VB/C# code?

HI Chris, I managed to…

StuartRedmore — Tue, 19 Mar 2019 16:07:00 GMT

HI Chris, I managed to acheive this recently by using a .net code stage. I added the collections to a data set, Created a data relation for the data set. Then loop throughed ""collection 1"" finding its children and added data to the row. I can post the code if you require. Regards

Hi Stuart, That sounds like…

ChrisHodgson1 — Tue, 02 Apr 2019 18:26:00 GMT

Hi Stuart, That sounds like it could be useful. I'd love to have a look at it if you don't mind posting the code. Thanks for your response. Chris

Hi Chris, I have attached it…

StuartRedmore — Wed, 03 Apr 2019 15:35:00 GMT

Hi Chris, I have attached it. The parameters are: In : Collection_One - Datatable In : Field_One_Name - String In : Collection_two - Datatable In : Field_Two_Name - String Out : Collection_Out - Datatable Out : DataTableLen - Decimal Naturally Datatables are the collections. The code I've written is basically the form of ""Left join"" , but it wouldn't be difficult to change to an inner join if that's what you needed. It only works on a single column to column relationship, these being Field_One_Name ( this must exist in Collection_One ), and Field_Two_Name ( this must exist in Collection_Two ). but again it wouldn't take much work to expand this. You can ignore / remove the DataTableLen item from the code and output, I added it whilst debugging and simply forgot to remove it. Please let me know if i can help further.

Hi Stuart, That object…

ChrisHodgson1 — Wed, 03 Apr 2019 16:11:00 GMT

Hi Stuart, That object worked perfectly! Thank you very much for your help I really appreciate it. Chris

RE: Hi Stuart, That object…

SaumyaSinha2 — Mon, 06 Dec 2021 13:58:00 GMT

Can you pls help me with the same code as well

------------------------------
Saumya Sinha
------------------------------