Group by employee column and Date coulmn as row

Help Something Else
, ,
1

would like group the table like below
Employee Date. Email
99899. 09/15/2024. Abc@test
99899. 09/16/2024. Abc@test
99899. 09/17/2024. Abc@test
89768. 09/15/2024. Anc@test
89768. 09/16/2024. Anc@teat

Employee . Email Date
99899. Abc@test. 09/15/2024,09/16/2024,09/17/2024
89768. Anc@test. 09/15/2024,09/16/2024

2

Do you want to do this with a script or with UiPath rpa?

If the answer is script then here you go :

SELECT
Employee,
Email,
STRING_AGG(Date, ‘,’) WITHIN GROUP (ORDER BY Date ASC) AS Dates
FROM
your_table
GROUP BY
Employee, Email;

Please mark this as a solution if it worked.

3

Hi @Jayaraman_Kumar

Can you try below

This image shows a UiPath sequence workflow that builds a data table, reads data from an Excel file, processes the data, and writes the result to a different sheet in the same Excel file. (Captioned by AI)
This image shows a UiPath sequence workflow that builds a data table, reads data from an Excel file, processes the data, and writes the result to a different sheet in the same Excel file. (Captioned by AI)818×591 30.6 KB

FinalDT = ((From row In InputDT.AsEnumerable()
 Group row By emp = row("Employee"), email = row("Email") Into Group
 Select FinalDT.Rows.Add(emp, email, String.Join(",", Group.Select(Function(g) DateTime.Parse(g("Date").ToString()).ToString("MM/dd/yyyy"))))).CopyToDataTable()

Sequence18.xaml (16.3 KB)

Input:

This is an Excel spreadsheet showing employee records with columns for Employee ID, Date, and Email. (Captioned by AI)

Output:

A spreadsheet with three columns labeled "Employee," "Email," and "Date" contains data for two employees, including their email addresses and a series of dates. (Captioned by AI)
A spreadsheet with three columns labeled "Employee," "Email," and "Date" contains data for two employees, including their email addresses and a series of dates. (Captioned by AI)715×381 5.1 KB

Regards,

4

I want to do it with UiPath RPA

5

ok, then you can use the command as shown by @Irtetala

6

Apologies for the late reply.
This query return duplicate entry.
The same data being captured twice.

7

Hi @Jayaraman_Kumar

Step1- Read range your excel, you will get dtIn
Step2- build data table , dt result - add same column as your input data table
Step 3 take assign
dtResult= (From row In dtIn.AsEnumerable()
Group row By Employee = row(“Employee”).ToString(), Email = row(“Email”).ToString() Into Group
Select dtIn.Clone().NewRow() With {
.Item(“Employee”) = Employee,
.Item(“Email”) = Email,
.Item(“Date”) = String.Join(“,”, Group.Select(Function(r) r(“Date”).ToString()).ToArray())
}).CopyToDataTable()

step4- write dtResult

Please verify and let me know :slight_smile:

Thank you !