topic Re: Filter Unique Values Collection in Product Forum

Filter Unique Values Collection

SebT — Tue, 30 Nov 2021 13:34:52 GMT

Hi,

I have a collection containing 500 rows from which i wish to filter/sort/identify all duplicates based on values in one of the columns and move those duplicates to a separate collection.

I believe this might be achieveable using the 'Filter Collection' action, or perhaps a code stage, however I am unaware of what syntax to use.

Any help would be greatly appreciated.

Thanks
Br,
Sebastian

Re: Filter Unique Values Collection

vinodchinthakin — Tue, 30 Nov 2021 13:45:14 GMT

Hi Sebastian,

You can utilize this VBO from DX.
Use Action Distinct to get Unique Values.
Use Not in Operator Action to get Duplicate values. Pass Unique value collection as Input collection

Re: Filter Unique Values Collection

devneetmohanty07 — Tue, 30 Nov 2021 13:50:41 GMT

Hi Seb T,

The fastest way to filter out unique values is to extend the Collection Manipulation VBO and you can add a new action named "Keep Unique Values". Pass one collection as an input parameter and pass another collection as an output parameter and use the below code in the code stage:

Output_Collection = Input_Collection.DefaultView.ToTable(True)

Please find the below solution for your reference:

Re: Filter Unique Values Collection

SebT — Tue, 30 Nov 2021 14:03:58 GMT

Hi @devneetmohanty07

Thank you for your answer. However, what I am trying to achieve is to get a collection containing only the duplicated values - not the unique values.

Re: Filter Unique Values Collection

devneetmohanty07 — Tue, 30 Nov 2021 14:38:33 GMT

I am really sorry that I misunderstood the question a bit. Yes you were absolutely right, the solution I provided you gives you unique values based on all the fields not only one. I have modified my solution a bit for your requirement. Please find the updated solution as shown below:

Pass one collection as an input parameter along with a text data item parameter called "Field Name" and pass another collection as an output parameter and use the below code in the code stage:

Output_Collection = Input_Collection.DefaultView.ToTable(True,Field_Name)

Please let me know if it solves your query. Once you get the unique values you can perhaps iterate to find the duplicate ones by comparing this against the unique set of values.

Re: Filter Unique Values Collection

SebT — Tue, 30 Nov 2021 14:57:11 GMT

@devneetmohanty07 @vinod chinthakindi

I might have phrased my question wrong. What I need is a collection containing all duplicated values that exists within a single collection.

As an example, lets say I have the following collection with the following values. What I would like to have is a filter/code stage that would move or copy the values 1 & 3 (since they are duplicates) to a separate collection. Hope this makes sense.

Re: Filter Unique Values Collection

devneetmohanty07 — Wed, 01 Dec 2021 01:41:47 GMT

Hi Seb,

We got confused there for a while probably from the heading of the post. However, knowing your requirement I have come up with a different approach altogether where we can use a LINQ object to get the duplicate records. You can create a new business object and add the below External References ('System.Data.DataSetExtensions.dll', 'System.Core.dll') and Namespace Imports ('System.Data.DataSetExtensions', 'System.LINQ') on the Page Description stage of your Initialize action for the LINQ queries to work properly. Also, ensure that the language is selected as 'Visual Basic':

Once you have the updated code options as shown above, create a new action named 'Get Duplicate Records' and pass two input arguments, Input Collection (Collection) and Field Name (Text). Based on the field name that you provide the duplicate records will be fetched from the Input Collection. Also set an Output parameter as Output Collection (Collection) for this action as shown below:

Add the code stage and use the below code with the input and out arguments as show:

Output_Collection = (From row In Input_Collection _
Group row By a = row(Field_Name).Trim.ToString Into grp = Group _
Where grp.Count>1 _

Select grp.ToList).SelectMany(Function(x) x).CopyToDataTable()

The run results are as follows:

Input Arguments:

Output Result:

You can publish the action and test the same from Process Studio. Let us know if this helps you out 🙂

Re: Filter Unique Values Collection

SebT — Wed, 01 Dec 2021 08:58:31 GMT

@devneetmohanty07

Works like a charm! Thank you very much for your help and thorough explanation!