topic Re: Count unique values in a collection in Product Forum

Count unique values in a collection

SamuelYeung — Fri, 16 Feb 2024 06:34:50 GMT

Hi Community,

After retrieving data from a website, I have a collection with some main fields, each having its own sub-fields. The sub-fields will only contain either 'SUCCESS' or 'FAIL'. I want to count the number of results and store them in two seperate data items.

i.e. Assume there are two main fields, each with 3 sub-fields, after the data collection, 5x SUCCESS and 1x FAIL is recorded. The current value of the SUCCESS should become 5

Thanks!

Re: Count unique values in a collection

John__Carter — Fri, 16 Feb 2024 10:12:22 GMT

Assuming the collections are not gigantic, I would just use a nested loop to work though the child collections, counting each success. Alternatively apply a filter to the child collections and count the resulting rows.

A more advanced option could be to convert the collection to JSON. Then devise a code stage to deserialize the string into a JSON object that you can then manipulate. The effort to do this probably isn't worth it though.

Re: Count unique values in a collection

michaeloneil — Fri, 16 Feb 2024 11:36:13 GMT

There isnt an action that counts the values in the collection but you can get the count where you know the value of what you want to count. As John Carter mentioned if its low numbers in the collection then simply loop and count but if you are getting high volumes then you can filter the 'success' values and the 'fail' values into their own collections and then count the number of rows in each. Use the VBO Utility - Collection Manipulation Extended and the action Filter collection, you set the collection you want to filter and the value of the column you want to filter by and the output collection. The next thing is to use the Internal Business Objects, Collections VBO and the action Count rows. You can then count the rows of the filtered collection, screenshots below.

Re: Count unique values in a collection

MichealCharron — Fri, 16 Feb 2024 14:04:22 GMT

@Samuel Yeung

Playing a little bit upon what @John Carter had suggested, you could use the "Collection To JSON" action in "Utility - JSON" to convert the collection to Text. Then, if you have a count occurrences action, you could count the occurrences of "FAIL" and "SUCCESS" (including the double quotes). If you do not have a count occurrences action, you could use the "Extract Regex All Matches" action in BP's "Utility - Strings" VBO on each of "FAIL" and "SUCCESS" (including the double quotes) and count the rows in each of the collections.

Re: Count unique values in a collection

SamuelYeung — Mon, 19 Feb 2024 01:00:47 GMT

@John Carter @Michael ONeil @Micheal Charron

Thanks for the answers!

Re: Count unique values in a collection

michaeloneil — Mon, 19 Feb 2024 10:31:18 GMT

@Samuel Yeung i thought about this again and realised that it might actually be useful to have an action that counts the number of instances of a specific value in the a specified column so I create a new action in the Utility - Collection Manipulation Extended if its useful to you?

The code is simple so you can just copy and paste it, in the Collection Manipulation vbo just duplicate the action 'Filter Collection' and then rename the new action to Count Occurrences. In the code stage delete the existing code and replace it with the following:

Dim count As Integer

For Each parentRow As DataRow In Collection_In.Select(Select_Condition)

count += 1

CountOccurrences = count

Then just delete the output collection and create a new output data item called Count. Since its based on the filter collection action you can just the inputs as they are and to count the collection your input parameters would be the collection and then the count criteria as "[columnname]='ValuetoCount'"