LinQ query help regarding duplicates

[Biswas_Ishan]

So I have a datatable which has many columns, but mainly at focus are two columns. “Associate ID” and “Effective Date”. I want to write a LinQ query to give me a datatable out of the existing by remove all the duplicates rows of same value of “Associate ID” but keeping only the latest row according to “Effective Date”. So suppose my Datatable looks like this:

It will delete the first row entry and give the rest of the datatble.

PS: Yes the effective date can have either MM-dd-yyyy or MM/dd/yyyy format.

So for this I have written the following code:

(From row In AdditionsReportDT.AsEnumerable()
                   Group row By associateID = row.Field(Of String)("Associate ID") Into Group = Group
                   Let maxDateRow = If(Group.Count() = 1,
                                       Group.First(),
                                       Group.OrderByDescending(Function(r As DataRow) DateTime.ParseExact(r.Field(Of String)("Effective Date"), {"MM-dd-yyyy", "MM/dd/yyyy"}, CultureInfo.InvariantCulture)).First())
                   Select maxDateRow).CopyToDataTable()

I am getting errors in this, please could someone help me, thanks in advance

[rlgandu]

@Biswas_Ishan

Dim distinctRows = (From row In AdditionsReportDT.AsEnumerable()
                    Group row By associateID = row.Field(Of String)("Associate ID") Into Group
                    Let maxDateRow = If(Group.Count() = 1,
                                        Group.First(),
                                        Group.OrderByDescending(Function(r) DateTime.ParseExact(r.Field(Of String)("Effective Date"), {"MM-dd-yyyy", "MM/dd/yyyy"}, CultureInfo.InvariantCulture)).First())
                    Select maxDateRow).CopyToDataTable()

[Parvathy (PS Parvathy)]

Hi @Biswas_Ishan

Try this:

(From row In AdditionsReportDT.AsEnumerable()
Group row By associateID = row.Field(Of String)("Associate ID") Into Group = Group
Let maxDateRow = If(Group.Count() = 1,
                    Group.First(),
                    Group.OrderByDescending(Function(r As DataRow) DateTime.ParseExact(r.Field(Of String)("Effective Date"), {"MM-dd-yyyy", "MM/dd/yyyy"}, System.GlobalizationCultureInfo.InvariantCulture, DateTimeStyles.None)).First())
Select maxDateRow).CopyToDataTable()

Hope it helps!!

[Biswas_Ishan]

Getting this error:

Error ERROR Validation Error Compiler error(s) encountered processing expression “Dim distinctRows = (From row In AdditionsReportDT.AsEnumerable()
Group row By associateID = row.Field(Of String)(“Associate ID”) Into Group
Let maxDateRow = If(Group.Count() = 1,
Group.First(),
Group.OrderByDescending(Function(r) DateTime.ParseExact(r.Field(Of String)(“Effective Date”), {“MM-dd-yyyy”, “MM/dd/yyyy”}, CultureInfo.InvariantCulture)).First())
Select maxDateRow).CopyToDataTable()”.
Expression expected. Test.xaml

[Biswas_Ishan]

Getting this:

Error ERROR Validation Error Compiler error(s) encountered processing expression “(From row In AdditionsReportDT.AsEnumerable()
Group row By associateID = row.Field(Of String)(“Associate ID”) Into Group = Group
Let maxDateRow = If(Group.Count() = 1,
Group.First(),
Group.OrderByDescending(Function(r As DataRow) DateTime.ParseExact(r.Field(Of String)(“Effective Date”), {“MM-dd-yyyy”, “MM/dd/yyyy”}, System.GlobalizationCultureInfo.InvariantCulture, DateTimeStyles.None)).First())
Select maxDateRow).CopyToDataTable()”.
‘GlobalizationCultureInfo’ is not a member of ‘System’.
Data type(s) of the type parameter(s) in extension method ‘Public Function OrderByDescending(Of TKey)(keySelector As System.Func(Of System.Data.DataRow, TKey)) As System.Linq.IOrderedEnumerable(Of System.Data.DataRow)’ defined in ‘System.Linq.Enumerable’ cannot be inferred from these arguments. Specifying the data type(s) explicitly might correct this error. Test.xaml

[rlgandu]

@Biswas_Ishan

distinctRows = (From row In AdditionsReportDT.AsEnumerable()
                    Group row By associateID = row.Field(Of String)("Associate ID") Into Group
                    Let maxDateRow = If(Group.Count() = 1,
                                        Group.First(),
                                        Group.OrderByDescending(Function(r) DateTime.ParseExact(r.Field(Of String)("Effective Date"), {"MM-dd-yyyy", "MM/dd/yyyy"}, System.Globalization.CultureInfo.InvariantCulture)).First())
                    Select maxDateRow).CopyToDataTable()

[Parvathy (PS Parvathy)]

Hi @Biswas_Ishan

Delete the assign activity and from Imports Panel Import System.Linq, System.Globalization, System.Linq . Expressions.

Let me know if you face any error.

Regards

[Biswas_Ishan]

It says

Error ERROR Validation Error No compiled code to run
error BC30311: Value of type ‘String()’ cannot be converted to ‘String’. At line 5 Test.xaml

[Biswas_Ishan]

Okay, imported these, now shall I keep your code in an assign?

[rlgandu]

@Biswas_Ishan

distinctRows = (From row In AdditionsReportDT.AsEnumerable()
                    Group row By associateID = row.Field(Of String())("Associate ID") Into Group
                    Let maxDateRow = If(Group.Count() = 1,
                                        Group.First(),
                                        Group.OrderByDescending(Function(r) DateTime.ParseExact(r.Field(Of String())("Effective Date"), {"MM-dd-yyyy", "MM/dd/yyyy"}, System.Globalization.CultureInfo.InvariantCulture)).First())
                    Select maxDateRow).CopyToDataTable()

[Parvathy (PS Parvathy)]

Yes you can keep the below code in assign @Biswas_Ishan

(From row In AdditionsReportDT.AsEnumerable()
Group row By associateID = row.Field(Of String)("Associate ID") Into Group = Group
Let maxDateRow = If(Group.Count() = 1,
                    Group.First(),
                    Group.OrderByDescending(Function(r As DataRow) DateTime.ParseExact(r.Field(Of String)("Effective Date"), {"MM-dd-yyyy", "MM/dd/yyyy"}, System.Globalization.CultureInfo.InvariantCulture, DateTimeStyles.None)).First())
Select maxDateRow).CopyToDataTable()

Hope it helps!!

[Biswas_Ishan]

Same error

[Biswas_Ishan]

This worked, no errors

[Biswas_Ishan]

But while running this error pops

Assign: String was not recognized as a valid DateTime.

[Rajiv_Mulchandani (Rajiv)]

try something like this to remove the Duplicates based on Associate ID

inputDatatable.asEnumerable.select(function (row) row(“Associate Id”).tostring).Select(function(x) x.first).copytoDatatable

[Parvathy (PS Parvathy)]

Hi @Biswas_Ishan

Try this:

(From row In AdditionsReportDT.AsEnumerable()
Group row By associateID = row.Field(Of String)("Associate ID") Into Group = Group
Let maxDateRow = If(Group.Count() = 1,
                    Group.First(),
                    Group.OrderByDescending(Function(r As DataRow) DateTime.ParseExact(r.Field(Of String)("Effective Date"), {"MM-dd-yyyy", "M/dd/yyyy"}, System.Globalization.CultureInfo.InvariantCulture, DateTimeStyles.None)).First())
Select maxDateRow).CopyToDataTable()

This should work
Regards

[Biswas_Ishan]

No but i want the latest associate id, according to effective date columns

[Parvathy (PS Parvathy)]

Hi @Biswas_Ishan

Try this:

LinQ query help regarding duplicates

Hi @Biswas_Ishan Try this: (From row In AdditionsReportDT.AsEnumerable() Group row By associateID = row.Field(Of String)("Associate ID") Into Group = Group Let maxDateRow = If(Group.Count() = 1, Group.First(), Group.OrderByDescending(Function(r As DataRow) DateTime.ParseExact(r.Field(Of String)("Effective Date"), {"MM-dd-yyyy", "M/dd/yyyy"}, System.Globalization.CultureInfo.InvariantCulture, DateTimeStyles.None)).First()) Select maxDateRow).CopyToDataTab…

Regards

[Biswas_Ishan]

Yes!!!

Now it finally works

[Parvathy (PS Parvathy)]

Perfect!! So the topic can be closed.

Forum FAQ - How to mark a post as a solution

Forum FAQ - How to mark a post as a solution This document is part of our beginners guide. This article will teach you how to properly mark a post as a solution. We were closely following our last UiPath Forum feedback round! topic and carefully extracted all the bits of feedback that was provided. As such, we would like to tackle the topic of solutions on our Forum and how to properly use them. When is the topic resolved? The topic can be considered resolved when the topic author has fo…

Happy Automation!!