LINQ Query--- Merge Column And Average Salary And Write In New Sheet

Help Studio
, ,
1

ID Name Department Salary
101 Mayuresh IT 30000
102 Vivak Cs 31000
103 pooja Sales 12345
104 Rohit IT 20000
105 Virat Sales 31143
106 Abhijeet CS 12345
107 Somnath Baf 12343

How Can I merge Each Department Column And Write in New Sheet

Department || Average Salary

Like This—
Department || Average Salary

 IT                     50,000

How i ca i solve Tis Using LINQ in ui Path

3

Hi @Mayuresh_Chavan ,

You can try Groupby of linq.

4

@Mayuresh_Chavan
This can be used for the solution give it a try

From row In dt.AsEnumerable()
Group row By department = row.Field(Of String)(“Department”) Into Group
Select New With {
Key department,
AverageSalary = Group.Average(Function(r) r.Field(Of Double)(“Salary”)

Then you can try building a new datatable

Dim resultTable As New DataTable
resultTable.Columns.Add(“Department”, GetType(String))
resultTable.Columns.Add(“Average Salary”, GetType(Double))

For Each item In groupedData
resultTable.Rows.Add(item.department, item.AverageSalary)
Next

Write the result in Excel using Write Range activity

Thanks & Regards

5

Error
Error1004×448 26.7 KB

Facing This Error

1 Like
6 
dtOutput = (From row In dt_1.AsEnumerable()
            Group row By Department = row.Field(Of String)("Department") Into Group
            Select dtOutput.LoadDataRow(New Object() {
                Department,
                Group.Average(Function(r) Convert.ToDecimal(r.Field(Of Double)("Salary")))
            }, False)).CopyToDataTable()

Try this code in assign activity .
prior to this use read range activity to read the input file (dt_1) , use build dataTable activity for output(dtOutput)having columns Department and Salary

1 Like