Filter the past 1 week data from the Excel Column

Help Studio
, ,
1

Hi team,

Could you help me in providing the Logic for filtering the Data for the last one week from the Excel. we have to filter data from Monday to Sunday.

For example, in an excel for one column we have a many dates are available either future or past dates and we have a huge amount of data is available.

So, if today is Monday (06-02-2023) we have to filter the data for the past one week (30-01-2023 to 5-01-2023). I need to filter for these dates in dynamic (Means if today is 13-02-2023 then we have to filter for the 06-02-2023 to 12-02-2023) and keep saved that data.

Thanks, in advance!

2

HI @srinusoft37

How about this expression

DateTime.ParseExact(yourString,"dd.MM.yyyy",System.Globalization.CultureInfo.InvariantCulture).AddDays(-CInt(currentItem)).ToString("dd-MM-yyyy")

Check out the XAML file

LastWeekDateExcel.xaml (10.5 KB)

image
image931×499 25.7 KB

Note : Instead of Assign activity use Excel Read Range activity followed by For each Row in data Table activity.

Regards
Gokul

3

Thanks for the Reply Gokul,

LastWeekDateExcel.xaml (20.5 KB)

I build the code and tried for the output. But I am not able to have the Exact results.

In the Excel attachment I have taken the some random dates

data.xlsx (7.4 KB)

Kindly help me on this error issue. Am I doing wrong…

Thanks in advance

4

Hi @srinusoft37 ,
Check this code for your reference

image

image

date1 = now.AddDays(-7).Date

arr_date = Enumerable.Range(0,8).Select(Function(i) date1.AddDays(i)).ToArray()

dt_clone=arr_date.Select(Function(d) dt_1.Clone.LoadDataRow({d.ToString(“dd-MM-yyyy”),“”},False)).CopyToDataTable()

dt=dt_1.AsEnumerable.where(function(x) dt_clone.AsEnumerable.any(function(y) x(“Column1”).ToString.Equals(y(“Column1”).ToString))).CopyToDataTable

image

Thanks

5

HI @srinusoft37

How about the expression?

DateTime.ParseExact(CurrentRow("Date").ToString.Trim,{"dd.MM.yyyy","d.MM.yyyy"}, System.Globalization.CultureInfo.InvariantCulture,System.Globalization.DateTimeStyles.None).AddDays(-CInt(currentItem)).ToString("dd-MM-yyyy")

You can simply add the any number of date formats inside the {“”,“”} and the formats should be separated by comma

The above expression will look into array of string and choose the apt format as per the input string and convert them and return you the output of desired format

Try with this updated XAML file

LastWeekDateExcel.xaml (18.5 KB)

Regards
Gokul

6

Thanks for the reply,

Could you please help me in sending the xmal you build. I am facing the issue of taking the variable type of the arr_Date.

Thanks in advance

7

oh sorry my bad, it’s array of datetime
image

filterdate.xaml (12.6 KB)

8

Thanks for the reply.

My bad the xmal you shared is not opening in my machine. I recreated the task and facing the below error.

image
image1291×716 39 KB

This is the excel i am using

data.xlsx (7.4 KB)

This is the xmal for the Process]

LastWeekDateExcel.xaml (24.6 KB)

Thanks in advance

9

@putera.timmy

Please help me on this issue.

Thanks in advance

10

Have you tried with this? @srinusoft37

11

yes, this is looping all the values and extracting the values which is not required,

My requirement is Lets take I have 1 or more columns. in this I have to filter for one column which contains dates. in this column we have to filter the values only on monday(06.02.2022) so all the dates available in this column from the (31-01-2023 to 04-02-2023) should be filter and saved in another data table.

For the above scenario above one, I am not able to get this requirement.

Thanks in advance

12

@srinusoft37
Because it is different format between arr_date and dt_1 from excel

image
image982×72 4.16 KB

Here I fix your code
image

LastWeekDateExcel.xaml (26.1 KB)

1 Like
13

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