Solved Comparing two Collections and and saving unique values.

Skauby

New Member
Hi,

I'm new to both coding and Blue Prism and i'm looking for some support.

I have a a project were I need to compare data from two collections ("Collection_1" and "Collection_2") and pick out any unique rows from "Collection_1" and save in "Collection_3".

Both collections only consists of one column which contains a Service ID.

I've tried with a standard Loop within a loop in BP, but since "Collection_1" and "Collection_2" both consists of over 22000 rows this is too slow.

I've looked around on different forums and it seems that my best bet for speed would be to create a code stage in an object that does this, I could really us some help in creating a code either in VBA or C# to do this.

I know it's a big ask, but any help would be appreciated.


Example:

"Collection_1"
Header: Service ID
4567
4566
4532
3455
9989
4312

"Collection_2"
Header: Service ID
4561
4562
9989
4312
4532
3455

Output:
"Collection_3"
Header: Unique Service ID
4567
4566
 

Skauby

New Member
I still haven't found a good solution for this so if there's anyone out there willing to help I would be grateful! :)
 

sahil_raina_91

Active Member
I still haven't found a good solution for this so if there's anyone out there willing to help I would be grateful! :)
Make sure to add below 3 Namespaces in Code Options Section :
System.Data
System.Collections.Generic
System.Linq

Make sure to add below 2 External References in Code Options Section :
System.Core.dll
System.Data.DataSetExtensions.dll

1619796383179.png
Create a code stage with Collection_1 & Collection_2 as inputs, & Collection_3 as output:
CODE(Yes, a single line is enough) : Collection_3 = Collection_2.AsEnumerable().Except(Collection_1.AsEnumerable(), DataRowComparer.Default).CopyToDatatable()

1619796435799.png
 
Last edited:
The above solution is correct, but it is not generic, if the columns are identical you can give a merge collection or an append of collection_2 to collection_1 and then you can use remove duplicates, so you can use as many collections as you want
 

sahil_raina_91

Active Member
The above solution is correct, but it is not generic, if the columns are identical you can give a merge collection or an append of collection_2 to collection_1 and then you can use remove duplicates, so you can use as many collections as you want
That would be an incorrect solution, besides the fact that it would take more time to execute.
Merging & removing duplicates will fetch all unique records from both collections. OP only wants unique values present in 1st collection that are not present in 2nd collection.
 

Skauby

New Member
That would be an incorrect solution, besides the fact that it would take more time to execute.
Merging & removing duplicates will fetch all unique records from both collections. OP only wants unique values present in 1st collection that are not present in 2nd collection.
Correct, Your solution worked like a charm and saved a lot of time as I'm dealing with two collections with two collections with 20k+ rows
 

Burks

New Member
Make sure to add below 3 Namespaces in Code Options Section :
System.Data
System.Collections.Generic
System.Linq

Make sure to add below 2 External References in Code Options Section :
System.Core.dll
System.Data.DataSetExtensions.dll

View attachment 5935
Create a code stage with Collection_1 & Collection_2 as inputs, & Collection_3 as output:
CODE(Yes, a single line is enough) : Collection_3 = Collection_2.AsEnumerable().Except(Collection_1.AsEnumerable(), DataRowComparer.Default).CopyToDatatable()

View attachment 5936
I'm new to BP - would you mind expanding on how I declare the variables for the data items for the collections please? I know I need to do it in the Global Code area but can't figure out how.
 

sahil_raina_91

Active Member
I'm new to BP - would you mind expanding on how I declare the variables for the data items for the collections please? I know I need to do it in the Global Code area but can't figure out how.
The code part is not to be done in Global Code area.
Instead create a new page, and inside that page add a code stage where the above code should reside.

1622739392685.png

The collection Data Items have to be created in the same page and should be passed as Inputs & Outputs to the code stage.

1622739449039.png
 

jjuniorwork

New Member
Hi, mister!
Compiler error at line 1: 'IEnumerable<DataRow>' does not contain a definition for 'CopyToDatatable' and no accessible extension method 'CopyToDatatable' accepting a first argument of type 'IEnumerable<DataRow>' could be found (are you missing a using directive or an assembly reference?)
can you help me?
 
Top