Count Number of Occurances per Variable in a List

Help Studio
1

Hello All,

I have a list of codes in an excel data table. In UIPath, I would like to count the occurances of for each code. For example if I have the below list, I would like the output to show me that there are 2 N1s, 1 N2, and 1 N3. I would also like to show 0 for codes that are not present, such as N4. Is this possible in UIPath, and if so what is the best way to go about it? Thanks!

N1
N2
N3
N1

2

1.) Create a new datatable without distinct values with the Remove Duplicates activity.
2.) Iterate over your new table and for each value in the table, get the count using DtOriginal.Select("[ColumnName] = '" + row(0).ToString + "').Count, where DtOriginal is your original datatable, ColumnName is the name of the column from your original table, and row is the row from your distinct datatable you created in step 1.

3

@amanda.b.derdiger
As an alternate
Create a new var dtResult of datatype Datatable
Use an assign and implement
dtResult =Yourorigindatatablevar.Clone

Add a new column with the Name e.g Count to dtResult

Now within assign
Legt Side: dtResult
Right Side:
(From d in yourorigindatatablevar.asenumerable
group d by k=d(0).toString.trim into grp=Group
Let ra = {k, grp.count}
Select dtResult.Rows.Add(ra)).CopyToDataTable

This should Return a statistic like
N1,2

1 Like
4

Hi ppr,

Thank you for this. I tried to write the code for the right side of the assign function, but I am getting the following error. Do you know what might be causing it?
image

Additionally the next step of this will be to use if functions to write out certain text depending on which codes are present and their count. Will this solution allow for that?

5

Hi Anthony,

Thank you for your help. Which activities are you using for the second step?

6

Give a try with this alternate Line within the Statement
Let ra = {d(0), grp.count}
Or
Let ra as Object() = {d(0), grp.count}

1 Like
7

Now it’s giving the error that ‘d’ is not declared. Is that a standard variable used or do I need to define it in my workflow variables?

8

Can you Copy your current entire Statement and variable panel settings here

9

image

10

@amanda.b.derdiger
Ah Bad, sorry i guess i got the issue use

Let ra = {grp.first()(0), grp.count}

1 Like
11

Hi, i think without seeing an example of your excel, will be hard to give you an exact answer… How can we tell which codes are not present?

1 Like
12

Here is an example of my input file. Essentially I need to count the number of occurance of each code. Then, I need to create a variable so that for example if there are instances of N11, I can type out something like “There are 3 instances of N11”

Input File Example.xlsx (10.4 KB)

13

@amanda.b.derdiger
with the assumption that there is realtionshot to Month a reference list can be built:
N1, N2, N3…N12

Based on this a count retrieval can be implemented with following result:
grafik

Find starter help here:
amanda.b.derdiger.xaml (8.5 KB)

2 Likes
14

Thank you all for your help!

I was able to find the solution in another post. I created variables for the Count of each code and used an assign activity with the following on the right:

DataTable.AsEnumerable().Where(Function(row) row(“Column Name”).ToString.Equals(“Code”)).ToArray().Count

1 Like
15

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