topic RE: Collection - Remove Duplicated Values in Specific column in Product Forum

Collection - Remove Duplicated Values in Specific column

MateuszWojcik — Wed, 04 Mar 2020 12:06:00 GMT

Hello,

I have action which remove duplicated rows in whole collection.
Collection_Out = Collection_In.DefaultView.ToTable(True)

I want to write new action which remove duplicates value only in specific column.

Does anyone have solution for this?

Thank you in advance

------------------------------
Mateusz Wojcik
RPA Programist
Rockwell Automation
Europe/Warsaw
------------------------------

RE: Collection - Remove Duplicated Values in Specific column

RaúlHernández — Wed, 04 Mar 2020 13:18:00 GMT

Hi, Mateusz.

You can try to use the asset from Digital Exchange called Millicom – Collection Utility.

You can find the action the extract unique values (or remove duplicates) from a collection and other nice actions to work with collections.

------Original Message------

Hello,

I have action which remove duplicated rows in whole collection.
Collection_Out = Collection_In.DefaultView.ToTable(True)

I want to write new action which remove duplicates value only in specific column.

Does anyone have solution for this?

Thank you in advance

------------------------------
Mateusz Wojcik
RPA Programist
Rockwell Automation
Europe/Warsaw
------------------------------

RE: Collection - Remove Duplicated Values in Specific column

IshanMahajan — Wed, 04 Mar 2020 13:26:00 GMT

Good Suggestion by Raúl Hernández, and if you are not allowed to import a VBO, you can sort your collection on the the same field where you expect duplicates, and run a loop on it as long as you find the currentrow.column value = lastrow.cloumn value, you can delete the current row.

or you can choose to write a code stage as well.

------------------------------
Ishan Mahajan
India
------------------------------

RE: Collection - Remove Duplicated Values in Specific column

MateuszWojcik — Wed, 04 Mar 2020 13:42:00 GMT

Raúl Hernández I do not see those actions in Millicom – Collection Utility. Please see attachment

Ishan Mahajan thank you for this solution.

I am wondering if there is a way to extand code Collection_Out = Collection_In.DefaultView.ToTable(True). This will contains less stages. than looping through whole collection.

------------------------------
Mateusz Wojcik
RPA Programist
Rockwell Automation
Europe/Warsaw
------------------------------

RE: Collection - Remove Duplicated Values in Specific column

RaúlHernández — Wed, 04 Mar 2020 13:43:00 GMT

I think you can use DISTINCT to get the result you need.

Try it and lets see if works.

------Original Message------

Raúl Hernández I do not see those actions in Millicom – Collection Utility. Please see attachment

------------------------------
Mateusz Wojcik
RPA Programist
Rockwell Automation
Europe/Warsaw
------------------------------

RE: Collection - Remove Duplicated Values in Specific column

MateuszWojcik — Wed, 04 Mar 2020 14:04:00 GMT

DISTINCT action return a collection with unique values and column counter
I lost others columns.

Please see attachments

------------------------------
Mateusz Wojcik
RPA Programist
Rockwell Automation
Europe/Warsaw
------------------------------

RE: Collection - Remove Duplicated Values in Specific column

RaúlHernández — Wed, 04 Mar 2020 15:14:00 GMT

I understand.

In that particular case, you can use a simple loop logic to get unique values from the collection.

Simple loop logic should use

A mirror collection, to compare all elements.
A new collection to store unique values (entire row).
A loop inside other loop, to compare all elements.
a Flag to identify if the element already exist in your new Collection.

This logic is high time consuming depending of the numbers of rows in your collection, but it will work.

------Original Message------

DISTINCT action return a collection with unique values and column counter
I lost others columns.

Please see attachments

------------------------------
Mateusz Wojcik
RPA Programist
Rockwell Automation
Europe/Warsaw
------------------------------

RE: Collection - Remove Duplicated Values in Specific column

MateuszWojcik — Thu, 05 Mar 2020 06:42:00 GMT

Raúl Hernández thank you for this solution.

I am looking for less time consuming way, like extanding code Collection_Out = Collection_In.DefaultView.ToTable(True) by additional argument or additional line.

------------------------------
Mateusz Wojcik
RPA Programist
Rockwell Automation
Europe/Warsaw
------------------------------

RE: Collection - Remove Duplicated Values in Specific column

GopalBhaire — Thu, 05 Mar 2020 08:05:00 GMT

Dim c As Integer
c=Cint(cNumber)
Collection_Out = Collection_In.AsEnumerable().GroupBy(Function(row) row(c)).[Select](Function(group) group.First()).CopyToDataTable()

Add cNumber as Number Input referring to column number starting from index 0
ALso add following to Imports

------------------------------
Gopal Bhaire
Analyst
Accenture
------------------------------

RE: Collection - Remove Duplicated Values in Specific column

MateuszWojcik — Thu, 05 Mar 2020 13:02:00 GMT

Hi Gopal Bhaire

This is want I was looking for.
It works.

Thank you very much.

------------------------------
Mateusz Wojcik
RPA Programist
Rockwell Automation
Europe/Warsaw
------------------------------

RE: Collection - Remove Duplicated Values in Specific column

IANNUGI — Thu, 02 Jul 2020 10:25:00 GMT

i have try your code but i got error, can you help me

------------------------------
IAN NUGI
------------------------------

RE: Collection - Remove Duplicated Values in Specific column

MateuszWojcik — Thu, 02 Jul 2020 12:48:00 GMT

Hi IAN NUGI,

I send word document where you can find whole page from object.
Please paste it in your object Utility - Collection manipulation.

Please let me know if it works

Kind regards

------------------------------
Mateusz Wojcik
RPA Programist
Rockwell Automation
Europe/Warsaw
------------------------------

RE: Collection - Remove Duplicated Values in Specific column

MIROSLAWGADEK — Fri, 03 Jul 2020 06:42:00 GMT

Hi,
I can share my code regarding removing duplicates:
Create a code stage.
input:
<type collection> Input name: CollectionIn
output:
<type collection> output name: CollectionOut
<type text> ErrorMessage

ErrorMessage = "";
CollectionOut = new DataTable();
DataTable dt1 = new DataTable();
try
            {


                dt1 = CollectionIn;

                DataTable dtMerged = dt1.AsEnumerable()
				.GroupBy(r => new {d= r.Field<string>("put here column name from your collection") })
				.Select(g => g.First()).CopyToDataTable();
                CollectionOut = dtMerged;
            }
				catch (Exception ex)
            {
                ErrorMessage = ex.Message.ToString();
                
            }

Namespace Imports:
System.Linq
System.Reflection
System.Collections.Generic

External References:
System.Linq.dll
System.Data.Common.dll
System.Core.dll
System.Data.DataSetExtensions.dll

Hope this will help:)

------------------------------
MIROSLAW GADEK
CI & QUALITY MANAGER DACH
Schneider Electric Operations Consulting GmbH
Europe/Berlin
------------------------------