Get the most repeated value in datatable

I have a variable “DataTable1”, Is it posible to get the most repeated value?

Example:
My datatable value:
blue
white
blue
blue
red

Output will become: blue

2 Likes

Hi @Chris_King_Baniqued

you can follow this link for your reference.

cheers :smiley:

Happy learning :smiley:

2 Likes

Hi @pattyricarte thanks for response, Is there other solution for that without using excel?

1 Like

Hi @Chris_King_Baniqued

Check this query

New Dt=Datatable1.DefaultView.toTable(true, “Column1”)

Thanks
Ashwin S

Hello @Chris_King_Baniqued
You can use this code in an assign activity and you’ll get all the duplicate values in a List

(DT.AsEnumerable().GroupBy(Function(r) cstr(r("ColumnName"))).Where(Function(g) g.Count>1).Select(function(n) n.Key)).tolist

Check this workflow for better understanding
Blue.xaml (6.4 KB)

1 Like

Hello @Chris_King_Baniqued
modifying @vickydas code a little bit…
DT.Select().GroupBy(Function(r) cstr(r("Column1"))).OrderByDescending(function(x) x.Count).First().Key

Hello @vickydas, Thanks for this, but it all return >1 value

Example:
Blue
Red
Blue
White
Blue
Red

Blue = 3
Red = 2
White = 1

It return Blue | Red

I want to return is only Blue.

Hi @AkshaySandhu, Thanks for response but your code gave me this error

use this in log message…
this will only give “blue” as string…

Hi @Chris_King_Baniqued

You can use the below given link to download the datatable plugin activity list. This activity list includes a activity named Consolidate Data table. In the activity, you can give the column name that has the color mentioned as the group by column and for the aggregations provide the aggregation as Count to count the number of occurrences.

Aggregation should go as
{({"ColorColumnName","Count"})}
This will count all occurrences of the color and group by by the color name column you specify.

Then simply sort the data table using sort data table activity in descending order and get the very first row which has the highest repeated value :slight_smile:

Use the manual given in the component for more info

Hope it helps

Yes, it works but how to get the value and store in a variable?

Use assign activity to assign this value to any string variable

Hello @Chris_King_Baniqued
use this code in assign Activity
(DT.AsEnumerable().GroupBy(Function(r) cstr(r("ColumnName"))).OrderByDescending(Function(g) g.Count()).Take(1).Select(function(n) n.Key)).tolist

1 Like

Hello @vickydas, Thanks a lot

Hey @Chris_King_Baniqued no offence but can you tell me how vickydas solution is differ than mine… in his solution you will be getting list of string containing only one item ( .Take(1).) with value “blue” and in mine solution you will be getting direct string value “blue”

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.