Fliter dt

Help
1

Hi everyone,

I need assistance for filter the data, in first sheet1 have the employee name,week,pay type and hours

below details needs to be filter and sum the value of hours based on paytype. i am using UIpath 8.1 only i do not have the data filter table

Employee Name Weeks Pay type Hours
ALSTON, ERIC L 10/14/2018 REGULAR 8.10
ALSTON, ERIC L 10/14/2018 REGULAR 8.25
ALSTON, ERIC L 10/14/2018 REGULAR 8.02
ALSTON, ERIC L 10/14/2018 REGULAR 11.50
ALSTON, ERIC L 10/14/2018 REGULAR 4.13
ALSTON, ERIC L 10/14/2018 REGULAR 7.37
ALSTON, ERIC L 10/14/2018 OVERTIME 8.00
ALSTON, ERIC L 10/14/2018 OVERTIME 8.03
ALLEN, RODRIEKA R 10/14/2018 REGULAR 8.22
ALLEN, RODRIEKA R 10/14/2018 REGULAR 8.25
ALLEN, RODRIEKA R 10/14/2018 REGULAR 8.25
ALLEN, RODRIEKA R 10/14/2018 REGULAR 9.62
ALLEN, BRYAN S 10/14/2018 OVERTIME 8.73
ALLEN, BRYAN S 10/14/2018 REGULAR 12.25
ALLEN, BRYAN S 10/14/2018 REGULAR 12.13
ALLEN, BRYAN S 10/14/2018 REGULAR 12.30
ALLEN, BRYAN S 10/14/2018 OVERTIME 3.32
ABDUL-MALIK, NAHSIER L 10/14/2018 OVERTIME 6.55
ABDUL-MALIK, NAHSIER L 10/14/2018 REGULAR 9.70
ABDUL-MALIK, NAHSIER L 10/14/2018 REGULAR 9.52
ABDUL-MALIK, NAHSIER L 10/14/2018 REGULAR 9.25
ABDUL-MALIK, NAHSIER L 10/14/2018 REGULAR 8.35
ABDUL-MALIK, NAHSIER L 10/14/2018 OVERTIME 3.18
2

Try the below expression in the assign activity

This is LINQ query equivalent in c# -

one for REGULAR, one for OVERTIME

datatable.AsEnumerable()
    .Where(y => y.Field<string>("Pay type") == "REGULAR")
    .Sum(x => x.Field<double>("Hours") )
    .ToString();
1 Like
3

Hello Christy,

You can use assign activity for this which should be like below.

Datatable.select(“PayType = ‘Regular’”)

Store this into array of datarow and then use for each activity for datarow and calculate sum of the desired column.

1 Like
4

sorry, i am new to the UIpath but i eager to learn, kindly explain till while detail

5

thanks for your support…

6

thanks for prompt response, i need more specific, because i am new to the UIpath.

7

hi, kindly reply

8

Hi Christy,

Can you explain what you want to filter?
How much training have you done in UiPath?

KarthikiBygari and ankytheman have told you how to filter for Pay type and hours, you do this in the “Assign” activity. This means that you make a new variable with the filtered data table, does that make sense? :slight_smile:

9

thanks but it is not worked then only make it clear

10

Sorry, I don’t understand what you mean :confused:

You need to use this activity:
image

In the VB expression you should write the filter expression, for example the one ankytheman mentioned.

11

Hi @christy,
You can use datatable select method as @ankytheman mentioned. Here I’m attaching the solved workflow for your problem.
Main.xaml (9.0 KB)

3
3466×1095 47 KB

1
11217×291 14.8 KB

2
21186×310 15.4 KB

Thanks!

1 Like
12

Hello @christy,

Let us know whether solution provided by @Sat is working for you… :neutral_face:

13

No, it is not working, your solution is very helpful but i need in detail, what is the logic behind to execute

14

any update on the solution

15

Hello Christy,

I will reply you with the solution and complete description. Will take some time for this.

16

Thanks for your valuable support

17

Hello Christy,

I am attaching a test.xaml file almost same as @Sat did. Please find below explanation for the attached code.

First Read Data From Excel File – Read Range(Either of Workbook or Excel).
Output of read range activity is EmployeeData(A Datatable)
An Assign Acivity is used to filter OVERTIME Data which will return array of datarows.
Another Assign Activity used to declare initial sum which is 0.
Applied for each loop to the array of data rows under which sum is taken for all the rows.

Same can be done for Regular as well. In that case Expression will be like below.

EmployeeData.Select(“Pay_type = ‘REGULAR’”)

Note - There Should not be any space in your headers on which you want to apply filter e.g. Pay Type should be PayType or Pay_type
You Should explore snippets provided by UiPath which can be seen in UiPath Studio.

Thank you :slight_smile:
_Test.xaml (8.8 KB)

18

Hello @KarthikByggari,

I am not able to use below expression as it is always prompting error in the expression box.

datatable.AsEnumerable() .Where(y => y.Field<string>(“Pay type”) == “REGULAR”) .Sum(x => x.Field<double>(“Hours”) ) .ToString();

Can you help please?

19

thanks kathik,

i need your support, test was not work for for each loop, kindly brief the loop activity for sum the data.

20

Hello Christy,

Please find attached an excel file on which sum is calculated. Place it on your desktop and replace user id with yours from the read range activity( in test.xaml which i provided earlier) and run the test.xaml. It will work absolutely fine.

EmployeeData.xlsx (11.0 KB)