How to get count of unique items in collection - Scenario.

[Tejaskumar_Darji]

Hello,

I have a collection with various numbers in each row. There can be duplicate values present.

What I want as output are 2 columns where I get all unique numbers in the 1st column, and in the 2nd column, I need a count of that number showing that how many times it is present in the collection.

e.g. Input Coll-
10
10
12
11
10
12


Output Coll-
10 |  3
11 |  1
12 | 2

Which VBO/Actions can be used to achieve this!

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

[HarpreetKaur · Accepted solution]

Hello Tejas,
There's some nested looping trickery that's required here.. Basically you want to pick up the value in the first row and loop through the collection and at the same time incrementing a counter as soon as you find a duplicate. Once the loop's done capture the value and the counter in a new collection. Now reset the counter and begin again.

Alternatively, you could also paste that column into an excel and apply some vlookup formulas..

Regards
Harpreet

------------------------------
Harpreet Kaur Product Consultant
------------------------------

[John__Carter]

I don't know of any ready-made action to do this but I think it can be achieved with a code stage using Linq.

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

[SAI_TEJAGAYALA]

Hi Harpreet,

If there are 10,000 records in the column to get count of unique items. can we still use this approach? Does it affect BOT performance?

------------------------------
SAI TEJA GAYALA
------------------------------

[devneetmohanty07]

Hi Sai,

For your use case, as John suggested I would go with a custom LINQ query object which you can create easily using below steps.

Firstly, create a business object called 'Utility - Collection (LINQ)' and then add the 'External References' and 'Namespace Imports' in your Initialise action's Page Description stage keeping the language as 'Visual Basic' as shown below:



Create a new action called 'Get Element Count' and add two input parameters named as 'Input Collection' of type 'Collection' and 'Field Name' of type 'Text' and map the data items accordingly. Also, add an output parameter called 'Output Collection' of type 'Collection' and map the data item as well. In addition you need to create a local data item called as 'Temp Collection' of type 'Collection' which has two default columns called 'Field' of 'Text' type and 'Count' of 'Number' type. You can refer the screenshot below:



Once done add a code stage called 'Get Element Count' and map the input parameters for 'Input Collection', 'Field Name' and 'Temp Collection' along with output parameter as 'Output Collection' to the code stage and add the following code:

Output_Collection = (From row In Input_Collection.AsEnumerable() Group By col1 = row(Field_Name).ToString() Into Group Select Temp_Collection.Rows.Add({col1, Group.Count()})).CopyToDataTable()





Now you can test the code once. As in my case I have an input collection with two fields called 'Field1' and 'Field2' with some random values as you can see below:


Let say I want the count of elements from Field1 so I will set the 'Field Name' input parameter as 'Field1' :


And you can see the results upon code execution:



​Hope it resolves your query!

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

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
Wonderbotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

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