How to group a set of comments

Hi Expert,
I can’t think of the solution, please help if you know.

I have a group of staff and there is feedback from the survey.
As you can see in the picture, i have Andy, Susan,Peter, Dickson and Rainy.
Each of them have a set of comments and the number of comments is not fix.
How do i tell Uipath to pick the comments belong to individual only? That output look like below:
Email output

The ultimate goal of this is for automate email, that can sent out the set of comments to individual in email. I can do email to replace variable but with a set of comments under 1 column i have no idea how to do it.

Thanks in advance

we can group by the name of staff column:

and while processing the groups we can prpare the email text and send

1 Like

Hi @Anneng

You can use group by to achieve this.

Thank you,
Prashant

Is it possible to give me a sample? I admit I’m really a baby on UiPath, less than 1 month. :frowning:
No time to learn crawling and now force to swim in deep ocean.

Group and produce a list of datatables (refer to the shared link)

Start with following:



grafik

Tablelist =

(From d In dtData.AsEnumerable
Group d By k=d("Name of the Staff").toString.Trim Into grp=Group 
Select grp.CopyToDataTable).toList

String.Join(Environment.NewLine, arrFeedback)

2 Likes

I tried but don’t understand and not able to do it. How do I write to range?

Hi @Anneng
you can try this apporach as well

  1. Read the excel and store in dt1

  2. Now use the below assign activity

dt2 = dt1.DefaultView.ToTable(True,“Name of the staff”), here dt2 will contains names of staff [unique values only]

now use for each row to loop through every name under dt2.[ for each row in dt2]

inisde it do the following

  1. Filter the dt1 with each name value from dt2 [ filter dt1 with value row(“Name of the staff”).ToString) and store output in dt3

  2. After getting the comments, u can use the email activity to mail the comments
    using following email body

"Hi “+ row(“Name of the staff”).ToString+” "+ String.Join(Environment.NewLine,dt3.AsEnumerable().Select(Function(row) row(“Feedback from survey”)).ToArray())

Try this way

Regards,
Nived N
Happy Automation

Hi Nived N,

I need help, i have this error.

I’m lost at this stage

  1. Filter the dt1 with each name value from dt2 [ filter dt1 with value row(“Name of the staff”).ToString) and store output in dt3

What function do you want me to use in the for each row body?

dt2 should be datatable

Also in for each row use dt2 instead of dttable

I’m sorry, I’m trying my best to understand:
Filter the dt1 with each name value from dt2 [ filter dt1 with value row(“Name of the staff”).ToString) and store output in dt3

I don’t think you mean in this way cause it don’t work. Can you give me a sample please?

Hi Peter,
I have tried your method, I have a lot of error on the assign variable. First is Tablelist. I have try and error, between string, datatable,generic all don’t work in the end i use list and the error Icon is disappeared.
Next is the arrFeedback I change to Array of String, error is gone. I have no idea am i doing the correct thing. Next I need to Write range but arrFeedback variable is not in system.datatable and how do i do it?

the issue comes from following:
arrFeedback is a string array and not a datatable. Therefore it cannot be used within the Write range. Give a try on using table representing the grouped data from current loop.

I see, I do see some result in the log messages output which excite me to see the result in the excel. How do i put it into datatable so that i can see the result in the excel?

What is to send to the member by email:

  • text with the listed comments
    OR
  • Excel in attachment (if Yes: 1Column - ColName=Comment?)

Hi Peter,

Really appreciate your help. I have been repeatedly reading the groupby sample in the attachment link.
The data i have is only two columns, column(0)(“Staff name”), Column(1)(“comments”)
Staff name is repeating and to their unique comments in the excel without sort.
So the goal is to group them like the example below:

Staff name | comments
Peter | You are helpful
| Friendly and polite
| You should be the manager
| You are very well groom and polite
Lynn | You need to improve your English
| You need to speak louder as the environment is too noisy.
…so on

I need output in excel because I need to lookup the email address to the staff name. In the last part is email

Hi Peter,
Below are your comments in the survey:

You are helpful
Friendly and polite
You should be the manager
You are very well groom and polite

Here is the sample file i created for testing.
emailcommentexample.xlsx (8.8 KB)

the name of the group that is currently processed you can get from
strName = table.Rows(0)(NameColIndexOrColName).toString

can be done as mentioned above by:
String.Join(Environment.NewLine, arrFeedback) - in case of Email is text type
String.Join("<br>", arrFeedback) - in case of Email is isHTMLBody

Setting up a template for the email subject have a look here where the main building blocks are introduced:

ok let me try it out.
thanks. Im trying to learn also.

Thank you very much, it work! Now I’m doing fine tune.

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