Extract data with filter

Help Studio
, ,
1

Hello everyone!
Please help me filter the data according to the following conditions: 1. If today is Monday (03/15/2021), then you need to extract the lines where the date is for last Friday (01/12/2021). 2. If today is Tuesday (03/16/2021), then retrieve data for the last Saturday, Sunday and Monday (03/13/2021 - 03/15/2021). 3. If today is Wednesday, Thursday or Friday, then display the data for yesterday.
Please help with filtering. Thanks!
FilterExcelByDateCol.xaml (15.3 KB)
FilterDate.xlsx (9.1 KB)

2

Hi,

you can check the day of the week like this:
today = DateTime.Today.DayOfWeek

Then, for example, if “today” is 1 (that indicates Monday) you can find the last Friday DateTime:
dayToSearch = DateTime.Today.AddDays(-3)

Then look up into the data table “dayToSearch”:
image

Or:

yourDataTable.AsEnumerable.Where(Function (x) x(“Date”).ToString Is dayToSearch.ToString(“d/M/yyyy”))

Could help something like this?

Regards, Gio

1 Like
3

Thanks. Yesterday I know how to get it. But I don’t know how to do this, if today is Tuesday, then search only for last Saturday, Sunday and Monday?
And how to retrieve only those columns that satisfy the condition? For example, today is Tuesday, so you need to retrieve data for Saturday, Sunday and Monday.
2021-03-23_13-45-45

4

Hi,

something like that:
yourDataTable.AsEnumerable.Where(Function(x) DateTime.Parse(x(“Date”).ToString) >daySaturday And DateTime.Parse(x(“Date”).ToString) < dayTuesday)

Regards, Gio

5

How do I extract columns that are highlighted? Please have a look at xaml. Thanks.

6

And in the “Number” column, the data type is text; when filtering, it is displayed incorrectly.

7

If you use this query:
yourDataTable.AsEnumerable.Where(Function(x) DateTime.Parse(x(“Date”).ToString) >daySaturday And DateTime.Parse(x(“Date”).ToString) < dayTuesday)

And you have to put:
daySaturday = DateTime.Today.AddDays(-3)
dayTuesday = DateTime.Today

So, for example, it takes the date bigger than 20.03.2021 00:00 and smaller than 23.03.2021 00:00.

Is it what you want?

8

I tried to filter out the data of yesterday (03/22/2021), but only the headers are written to the new sheet.

2021-03-23_15-48-22
2021-03-23_15-48-22456×755 22.4 KB

2021-03-23_15-51-21
2021-03-23_15-53-53
2021-03-23_15-53-53753×385 8.24 KB

FilterExcelByDateCol.xaml (14.8 KB)

9

Hi,

my solution uses LINQ.

In your solution, maybe in the “Filter Data Table” activity is a mismatch of data type. Did you check raw data in debug?

Gio

10

Thanks for your help! Made it through a database filter. The values ​​were not filtered due to different date formats, this point was fixed.

11

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.