How do I get Total Number of Records and Unique Values?

Help StudioX
, , ,
1

Hello,

I’m fairly new to UiPath and I’m trying to get the Total Number of Rows from an excel file as well as the number of Unique Values in a certain column. I then have to insert these values into an email.

So, per my screen shot below There are 25 Total Records, 16 are Commercial and 9 are Retail.

image

Thanks,

2

Hi @Tschultz

Just to confirm regarding unique values?

Do you want the unique values and their seperate count Or how many unique values present?

3

Only two values are present, Retail or Commercial. I need the total counts for each:

25 Total Records, 16 are Commercial and 9 are Retail

4

@Tschultz

Read the data from excel inti datatable(dt) using read range …

Use assign…create a variable of type integer total,commercial and retail

Total = dt.RowCount

Use filter datatable and filter column on commercial …then get the count again…

Commercial=Filtereddt.RowCount

Retail = total - commerical

If values vary then let us know…we can give more dynamic way

Cheers

5

Hi @Tschultz

Note : here I am creating a datatable which would be having category name on column and their count in other column. the last row in that datatable would be having total count of datatable rows

Try this

  1. Create a datatable with 2 columns Name and Value store in a variable dt1

  2. Read the excel file and store in a variable dt2

  3. Now use below assign activity

dt1 = (From row in dt2.AsEnumerable
Group row by k1= row(0).ToString Into Group
Select dt1.Rows.Add({k1,Group.Count})). CopyToDataTable

  1. Add a data row to dt1 with values {“Total”,dt2.Rows.Count}

Now u will get a dt1 datatable having values of individual category and total values at last row

Hope this helps :blush:

Mark it as solution if it solve ur problem

Thanks and regards
Nived N

6

I’m sorry I’m not following. I’m new to UiPath, just about 2 weeks, and breezing through Academy courses as quickly as possible.

Am I supposed to put that text in Advanced Editor? I’m not sure how to create a datatable.

7

Is this what you were asking me to do?

image
image464×591 13.7 KB

image

8

@Tschultz

Perfect…

Now total.RowCount is total count
similarly Commerical.RowCount and Retail.RowCount will give you the remaining counts

cheers

9

Oh, Okay now I get what you are saying with the total.RowCount, etc.

So, I’m trying to put those values into my email but I’m getting an error in my email.
Send Email: Object reference not set to an instance of an object.

image

image
image789×446 12.1 KB

image

10

@Tschultz

Are you using this inside an outlook scope activity?

cheers

11

Yes, Use Desktop Outlook App. It worked fine before I added those fields.

12

@Tschultz

Add 3 log messages before send mail and in each write each of the rowcount…then we can see what is empty…basically the error says that some variable value is null…is the range H:H correct?

Cheers

13

Ah, I had the email outside of the Use Excel. Once I put the email inside It worked!

1 Like
14

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