How to filter the data and add them

Book1.xlsx (8.5 KB)
Here is the sample file,How to add the data only the dept IT and age should be 21.How to add that data.

2 Likes

Hi

If you want only the filtered value then use a assign activity like this

dt = dt.AsEnumerable().Where(Function(a) a(â€śdeptâ€ť).ToString.Contains(â€śITâ€ť) AND a(â€śageâ€ť).ToString.Contains(â€ś21â€ť)).CopyToDatatable()

This gives the filtered datatable

Now to get the sum

Use another assign activity like this

Int_sum = dt.AsEnumerable().Sum(Function(row) cint(row(â€śsalaryâ€ť)))

Where int_sum is a variable of type int32

To have them in one expression then use a assign activity

int_sum = dt.AsEnumerable().Where(Function(a) a(â€śdeptâ€ť).ToString.Contains(â€śITâ€ť) AND a(â€śageâ€ť).ToString.Contains(â€ś21â€ť)).Sum(Function(row) CInt(row(â€śsalaryâ€ť).ToString))

Hope this helps

Cheers @anjani_priya

@anjani_priya

hi

for getting sum use the below expression

sumtotal=dt1.AsEnumerable.Where(Function(x) x(3).ToString.trim.Equals(â€śITâ€ť) and cint(x(1).ToString.trim)=21).Sum(Function(a) cint(a(2).ToString))

here sumtotal is the int32 datatype

to get the datatable after filtering, use the below expression

dt1.AsEnumerable.Where(Function(x) x(3).ToString.trim.Equals(â€śITâ€ť) and cint(x(1).ToString.trim)=21).copytodatatable

hope this helps

@anjani_priya

please use this to get the required sum

`sum = dt.AsEnumerable.Where(function(x) x("dept").ToString.Equals("IT") And x("age").ToString.Equals("21")).Sum(function(x) CDBL(x("salary").ToString))`

here sum is a variable is of type double

Hope this helps

cheers

Canâ€™t I use pivot table? and do this process.If yes,how to do.Send the sample code?

@anjani_priya

May I know why do you need a pivot?

if you need to do as pivot please use create pivot table activity and pass the rows and columns as you need

cheers

We can directly do with a simple linq like this

@anjani_priya

The value should be dynamically given.how to give that.
If we have 100 rows but we need only the sum of salary whose dept is IT.How to sum them?

@anjani_priya

can you explain what was your scenario

i think you no need to create pivot

@anjani_priya

ya it is possible

try with the expression given

or refer this

sumofsalary.zip (4.5 KB)

@anjani_priya

the above formula will give sum of any number of rowsâ€¦

if you want to pass variables in place of age and department name can use them as well

`sum = dt.AsEnumerable.Where(function(x) x("dept").ToString.Equals(var_Department) And x("age").ToString.Equals(var_Age)).Sum(function(x) CDBL(x("salary").ToString))`

var_Department,var_Age are two string type variables

cheers

Book1.xlsx (11.0 KB)

requirement is I want sum of salary of every dept separately
THAT SHOULD PASTE IN SHEET2

@anjani_priya

dt1.AsEnumerable.Where(Function(x) x(â€śdeptâ€ť).ToString.trim.Equals(â€śITâ€ť) and cint(x(â€śageâ€ť).ToString.trim)=21).copytodatatable

gives only the rows which are having Dept has IT

or you want all the salary of the dept=IT in to an array

arr_salary=dt1.AsEnumerable.where(Function(x) x(3).ToString.trim.Equals(â€śITâ€ť) And CInt(x(1).ToString.trim)=21).Select(Function(x) x(â€śSalaryâ€ť).tostring).toarray

here arr_salary is array of string

to get all the salaries in a single line use below expression

string.join(â€ś,â€ť,arr_salaray)

The age changes dynamically
if we done pivot the the values will be filtered
the filtered values should be in sheet2 how to do that

@anjani_priya

dt2=dt1.AsEnumerable.Where(Function(x) x(â€śdeptâ€ť).ToString.trim.Equals(â€śITâ€ť)).copytodatatable

use write range workbook activity to write the datatable into sheet2

@anjani_priya

Then you can use this

Create a datatable with two columns 1 columns is department a string type and othe column total of doubel type

Now use the following

`Newdt = (From d In DT.AsEnumerable Group d By k=d("dept").toString.Trim Into grp = Group Let c = grp.Sum(function(x) CDBL(x("salary").ToString)) Let ra = New Object(){k,c} Select r = Newdt.Rows.Add(ra)).CopyToDataTable`

This will give you a datatable with eqch department name and the salary sum

If you want sum of each department and age together then in build include one more column age

`Newdt = (From d In DT.AsEnumerable Group d By k=d("dept").toString.Trim,k1=d("age").ToString Into grp = Group Let c = grp.Sum(function(x) CDBL(x("salary").ToString)) Let ra = New Object(){k,k1,c} Select r = Newdt.Rows.Add(ra)).CopyToDataTable`

Cheers

Book1.xlsx (11.0 KB)
From sheet1 all the data should be of salary should be add dept wise and should fill in sheet2
how to do it
send the sample code?

@anjani_priya

Use the above code in assign and use write range

Cheers

can you send the sample program i cant understand the code

I have used pivot table and I did sum of all the data dept wise how to paste that data in sheet2