How to get unique values from a collection

Hi All,

I am having a collection with 7 Columns attached collection screen shot.
I need to get all the unique values specific to "ACE - Retro. Contract" column.

Thanks in Advance!!!
 

Attachments

  • Collection data.png
    Collection data.png
    33.9 KB · Views: 899

VJR

Well-Known Member
Hi Naveen,

You can get the unique values from a specific column by doing the below

Refer the attached screenshots-

1.jpg: Contains the process flow diagram
2.jpg: How the Original Input collection looks like and the duplicates are highlighted in yellow
3.jpg: Inputs for checking if value exists
4.jpg: Outputs for checking if value exists
5.jpg: Add a new blank row to the output collection
6.jpg: Write the column value to the output collection
7.jpg: Output collection at design time
8.jpg: How the output collection looks like after running the process. The duplicate values are removed and only unique values remain for Column 2 of original collection. If you want to copy entire row from original collection to output collection then you can do that too.
 

Attachments

  • 1. Process diagram.JPG
    1. Process diagram.JPG
    39.5 KB · Views: 3,642
  • 2. Original Input collection.JPG
    2. Original Input collection.JPG
    54.4 KB · Views: 3,470
  • 3. Contains value Inputs.JPG
    3. Contains value Inputs.JPG
    41.6 KB · Views: 3,379
  • 4. Contains value outputs.JPG
    4. Contains value outputs.JPG
    31.5 KB · Views: 3,092
  • 5. New row to Output collection.JPG
    5. New row to Output collection.JPG
    28.6 KB · Views: 2,880
  • 6. Write the value to output collection.JPG
    6. Write the value to output collection.JPG
    28.4 KB · Views: 2,740
  • 7. Output Collection.JPG
    7. Output Collection.JPG
    20 KB · Views: 2,545
  • 8. Output Collection Unique values after running.JPG
    8. Output Collection Unique values after running.JPG
    23.7 KB · Views: 2,545

Balaji R

New Member
What is the Expression for Decision Stage(If Found?)?

In the above attachments there is no image for Decision Stage. Please upload it or reply me with an expression.
 

VJR

Well-Known Member
Hi Balaji R,

As you can see in screenshot #4, 'Found' is a data item of type Flag - meaning the one that returns True or False.
The expression in the Decision stage is straight forward to check if the value of this data item is True or False and hence is not included in the screenshots. Would you want to research, explore and have a learning experience on how a Decision stage is written in Blue Prism?
 

Balaji R

New Member
Hi Balaji R,

As you can see in screenshot #4, 'Found' is a data item of type Flag - meaning the one that returns True or False.
The expression in the Decision stage is straight forward to check if the value of this data item is True or False and hence is not included in the screenshots. Would you want to research, explore and have a learning experience on how a Decision stage is written in Blue Prism?


Thanks for you reply. I'm new to Blue Prism and keen on learning how blue prism works.
 

VJR

Well-Known Member
Thanks for you reply. I'm new to Blue Prism and keen on learning how blue prism works.
Yes buddy, for the exact same reason of learning about how it works I've referred you to identify and come up with how the Decision stage is used to compare a flag item. Here is how it should look like..
[Found] =True to check if the Found data item is True.
 
Last edited:

VJR

Well-Known Member
Hi Balaji R,

This is a thread for "getting unique values from a collection" so I'm sure you wanted to post about Command Prompt on a different thread. Would you mind doing that please so that you get a suitable response to your query.
 

Balaji R

New Member
Hi VJR,

How to find a unique values for the below scenario:
1) Same names but different values
2) Same names & same values.

PFA.
 

Attachments

  • Data.png
    Data.png
    62.8 KB · Views: 486

VJR

Well-Known Member
Hi Balaji R,

One option would be to add a new column to the Original collection that will act as a combined key for that row.
This new column will contain the combination of Trainer and Salary columns as below.
1524549755532.png
(The duplicates are marked in Yellow and Blue)

- The new column can be added from the BP action. It is up to you where to add the new column and the data for it, whether in the Excel or in the Collection, but later has to be deleted.
- This will involve looping and adding the data to the new column. So this approach will be feasible to you only if you have less rows in the collection but I will explain it below and you can make the decision.
-Once the data is populated in the combined column, make a copy of the Original collection (single action in Blue Prism) and lets say it is called as the Destination collection.
-Loop on the Original collection, check if the Combined column of the Original collection is present in the combined column of the Destination collection. This can be done by the same 'Collection contains value' action as explained in the above post.
- If Found delete that row from the Destination collection. Again this is a single action available in Blue Prism
- Once the loop is over the resulting Destination collection will be the one with unique data.
- Delete the Combined column. To delete a column of collection is a single action available in BP.

Another quicker and best approach is the code stage.
Write a code that will mimic the already existing Remove Duplicates functionality of Excel.
From the Code you can call the Remove Duplicates method of Excel.

When manually done as below will give the same results when done through the code since we are calling the same functionality.

Remove Duplicates:

1524550638876.png



Before:

1524550591249.png

After:

1524550711240.png

The duplicate rows marked in Yellow and Blue above are now only appearing once.
 

smithmrk

Member
What if my column that has the duplicates is a NUMBER?
How can I convert the Number to Text or is there a way to find duplicate numbers?

Mark
 

VJR

Well-Known Member
Hi smithmrk,

There are multiple solutions in this thread. Can you point the post number as to for which you are referring as not working for a number.
 

RichCab

New Member
Hi All

I know this is an old thread but I wanted to contribute to this issue, I needed to look for duplicate entries in a collection, and I needed to check every column for every row to find any duplicate entries and management didn't want me to code anything outside of the utilities provided by Blue Prism to keep maintenance easy...
These are the steps I followed
1. Save Collection as CSV with the strings utility
2. Split lines to get the CSV into a one column collection
3. Used the method described by VJR in the first response to look for any duplicates and remove them
4. Convert the resulting collection back to CSV
5. Remove the first word Value and all double quotes that where added in step 4
6. Converting the CSV into the final collection, this one with the headers to keep the same data structure as the original
7. Remove Empty Rows using collection manipulation utility

I think this can be reused with any collection you need to check for duplicate entries
 
Top