Comparing two Collections to look for unique values

[DavidWood]

Hi all!

Hoping for some help. I'm quite new to Blue Prism and development in general so I'm a little stuck.

I'm trying to compare two Collections to determine - Common values in both, Unique Values in Collection 1, and Unique values in Collection 2.

Then take the comparison and put them into three new Collections

I have the common values in both by doing a loop within a loop then a decision stage to say [Collection1.Value]=[Collection2.Value]. If yes, Action stage to add row to new collection, then calculation stage to populate that row with [Collection1.Value]

The trouble comes with the other two sections. I just can't seem to find out the logic for this.

Is there a way to compare two collections to look for unique values in one of them?

------------------------------
David Wood
------------------------------

[NicholasZejdlik]

If you're looking for values that are in Collection 1 but not Collection 2, then you could use a loop within a loop and use Remove Row to remove values in Collection 1 as they are encountered in Collection 2 (or vice-versa). 

If by unique values you mean values that don't repeat themselves within a single collection, you'll need to use a count to keep track of the number of times a value has appeared, then remove values that occur more than once. (Alternatively, one could write a generic code stage to make this easier.)


------------------------------
Nicholas Zejdlik
RPA Developer
------------------------------

[John__Carter]

Hi David - another possibility is to merge the 2 together and then reduce the result down to unique rows - assuming the 2 have the same columns.

From memory the reduction part is not available in the collection utility, but can be achieved with a not too complicated code stage. In a code stage a BP collection is a .Net DataTable, and you can use that to create a DataView object. The DataView class has a method called ToTable which can filter on unique rows. Obviously you'll need to be comfortable with .Net code to achieve this, but if you are then it's worth investigating whether it will solve your problem.

------------------------------
John Carter
Professional Services
Blue Prism
------------------------------

[WasimAkram1]

Hi,

I am looking for help with same issue. 
Unable to  find Remove Row option in Utility-Collection Manipulation
I just got to remove the matching Rows in Collection 1, Both collection have same column names.

------------------------------
Wasim Akram
------------------------------

[Tejaskumar_Darji]

Hello,

One of the better solution to this problem is to use the "Collection contains value" action from VBO Utility-Collection. It returns a flag as output, so if it is false you can remove the row as per the above diagram and if it is true you can continue your loop. This way it can save time to run 2 loops and return you the perfect unique items present in both collections.


So run loop on Collection 1 and use Collection 2 in Collection contains value stage.

As output, you'll get common items present in both collections. In a way, we did inner join in this scenario.

Thanks


------------------------------
Thanks & Regards,
Tejaskumar Darji
Sr. RPA Consultant-Automation Developer
------------------------------

[lakshmisainandipati]

Hi Tejaskumar,

Thanks for your answer. But I have one concern on this. Suppose let's take there are 2 Collections : Collection 1 (5000 rows) and Collection 2 (10 rows) in which I wanted to remove the matching row from Collection 1. So, I have used Loop stage for Collection 1 and then Utility Collection manipulation VBO under that Action as Filter Collection: Collection 2 and filter collection condition is to check the matching row from both the collections and deleting the matching row from Collection1. But this is time consuming. Could you please help me how to achieve in simplest way.

------------------------------
Lakshmi Sai Nandipati
Application Developer
IBM
------------------------------

[devneetmohanty07]

Hi Lakshmi,

In order to work with huge collections while performing an operation where you require the common rows the best approach would be to develop a simple code stage using LINQ's. You can create a custom business object called 'Utility - Collection (LINQ)'.

- In the 'Initialise' action, go to the Page Description stage and add the following External References under 'Code Options' tab: System.Core.dll and System.Data.DataSetExtensions.dll along with the Namespace Imports as System.Data.DataSetExtensions and System.LINQ . Also, choose the Language as Visual Basic




- Now, create a custom action called 'Get Common Rows' with two input parameters called: Input Collection One and Input Collection Two and map the same to two collection stages with the same name. Also, create an output parameter called Output Collection and map the same to another collection stage.




- Next, drag a code stage with input arguments as 'Input Collection One' and 'Input Collection Two' and output argument as 'Output Collection' and map all the respective collection stages as shown below.







- Add the following code under the 'Code' tab of the code stage:

Output_Collection = Input_Collection_One.AsEnumerable().Intersect(Input_Collection_Two.AsEnumerable(),DataRowComparer.Default).CopyToDataTable()




- Publish the action, save and close the business object and then use the same from Process Studio for your testing.

Test Results: 

Test Scenario:-

In our case, we have two collections with 4 rows in total each out of which 3 rows are common in both.

Inputs for the Action:-





Outputs for the action:-

Let us know if it helps to resolve your query.

------------------------------
----------------------------------

Regards,
Devneet Mohanty
Intelligent Automation Consultant
Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------