Select DataTable

Help Studio
, , ,
1

Hello, I have the following table that contains data for a week.
image
The goal is to filter column “b” by day and column “c” by different values to get how many rows there are for a given day by value from column “c”.
with dt. Select(“[2]=‘Block/activate account’”). CopyToDataTable.Rows.Count.ToString I manage to filter the value in column “c” , but how do I filter it only for a specific day from column “b” every day? I tried with DT. Select(“[1]=‘dayofweek. Monday’”). CopyToDataTable.Rows.Count.ToString but not obtainable

2

@pl.rusinov

You can try like this

Dt.AsEnumerable.Where(function(x) Cdate(x(1).ToString).DayOfWeek = Now.DayOfWeek And x(2).ToString.Equals("Block/activate account")).Count

Will give the count after filtering both

Cheers

3

now.dayofweek gives the current day, and the table is for the whole of last week. in this case 06.03.2023 is Monday

4

@pl.rusinov

Dayofweek will give the day…may I know what you want to filter on?

Is it not on the day? Is it one some specific date?

Like Now.DayOfWeek today will give Wednesday…and it will filter on all wednesday dates

Cheers

5

Hi @pl.rusinov
is this the result you expected

Input file

image
image1151×633 12.5 KB

Here I am grouping by B column with respect to Day of week, and c column with values.

The result i got

image
image277×649 18.2 KB

There are 3 columns, Day , C, count,

I hope this is the expected result

I have used groupby query here

Linq_Groupby.xaml (8.5 KB)

Let me know if you have any questions

Thanks & Regards,
Nived N

6

All right, I’ll be clearer. I need to filter column “b” by date and column “c” by value. For example: filter column “b” on 06.03.2023=Monday and column “c” with value “block/activate”, filter column “b” on 06.03.2023=Monday and column “c” with value “close account”, filter column “b” on 07.03.2023=Tuesday with column “c” with condition “block/activate”, etc. for the whole week

7

Hi,

Try this linq,
First the column B will be filtered by day of the week starting from monday then filter the rows by column C

filteredData = dataTable.AsEnumerable()
    .Where(row => row.Field<DateTime>("b").Date >= monday.Date && row.Field<DateTime>("b").Date <= sunday.Date)
    .Where(row => (row.Field<string>("c") == "block/activate" && row.Field<DateTime>("b").DayOfWeek == DayOfWeek.Monday) || (row.Field<string>("c") == "close account" && row.Field<DateTime>("b").DayOfWeek == DayOfWeek.Monday) || (row.Field<string>("c") == "block/activate" && row.Field<DateTime>("b").DayOfWeek != DayOfWeek.Monday))
    .ToDataTable();```
8

@pl.rusinov

Does the groupby code helped?

9

@pl.rusinov

Then instead of now.dayofweek directly use which day you want

Dt.AsEnumerable.Where(function(x) Cdate(x(1).ToString).DayOfWeek.ToString.Equals("Monday") And x(2).ToString.Equals("Block/activate account")).Count

Hope this helps

Cheers

10

Hello, it seems to work according to my expectations somewhat. I can’t understand why it’s giving me the wrong data. For example, for day 06.03.2023 from column “B” and the value "blocking/activation from column “C” gives me 63 pieces, and there are 103

image

image

11

@pl.rusinov

Please try this, may be the casing of the values is differrent or the values might have extra spaces modified to trim and convert casing and check

Dt.AsEnumerable.Where(function(x) Cdate(x(1).ToString).DayOfWeek.ToString.Equals("Monday") And x(2).ToString.ToLower.Trim.Contains("block/activate account")).Count

cheers

12

not. now outputs a value of 0

13

@pl.rusinov

Can you show what exactly you tried…because I see you are using the data differently and also if you can share the data from locals panel how it looks we can accordingly change the script

I included a .ToLower which converts to lower type whatever you write inside the contains should be in lower case

Cheers

14

I use this condition. I’ve only changed the column numbers and condition.

image
image1065×54 5.34 KB

image
image1295×371 14.5 KB

image
image1537×87 6.46 KB

15

@pl.rusinov

In conditions did you consider .ToLower and changed the checking value completely to lower case?

Cheers

16

yes. That’s why I use

image
image991×54 5.05 KB

but gives the wrong value

17

@pl.rusinov

Use a .trim after .ToString and also try .contains instead of .equals

Not sure if .Tolower takes effect in the language you are using so use accordingly

Cheers

18

Unfortunately, this is not the case…

19

@pl.rusinov

Is it possible to share a sample file may be with only some data …I can try from my aide to understand the problem better… instead i would suggest run the bot in debug mode…pause it before filtering and open the datatable from locals panel…copy the value of C column properly and then use the same in the filter code

Cheers