How to filter the data and add them

Help Studio
, ,
1

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
2

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

3

@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

4

@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

5

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

6

@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

7

We can directly do with a simple linq like this

@anjani_priya

8

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?

9

@anjani_priya

can you explain what was your scenario

i think you no need to create pivot

10

@anjani_priya

ya it is possible

try with the expression given

or refer this

sumofsalary.zip (4.5 KB)

11

@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

12

Book1.xlsx (11.0 KB)

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

13

@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)

14

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

15

@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

16

@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

17

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?

18

@anjani_priya

Use the above code in assign and use write range

Cheers

19

can you send the sample program i cant understand the code

20

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