Trying to compare dates in a column using Filter data table

Help Community
,
1

Hi, I have a Date value which is in string format. I want to compare the string with a column in datatable which has Dates. But, I’m not sure how to do it using filter data table activity. My input is in string type but it is actually a date. I want to compare it with each rows of a column and filter the ones which are greater than the given input. I’ve went through some of the previous forums which is similar but nothing worked out, the filtered output has no data.

2

You can convert your string date to Date value using Cdate or Date.parse or datetime.parse and then you can compare with your datatable values…

Any sample data or sheet would help to analyze here…Thanks…

Just Some Hints…

image

3

Hi @prasath17, Thank you for your effort and time! Attaching the sample Excel file. My input string to compare is “5-27-2021”. The excel has column values in text format. Will the filter data table work if i compare it with the string which is converted to Date format?

4

I dont see any attachments…I guess you missed to attach…

You have convert that text to date value using Datetime.parseexact or datetime.parse methods and then compare with your string(convert it date for comparision) value…

5

Apologies. Attaching the sample file
Sample.xlsx (30.2 KB)
I’ve manually changed the format of entire column to Date but, the output of Filtered data table is empty.

6

@AswinSridhar - Thanks…what is your filtering condition??

7

You can first parse the string to a date as @prasath17 said, then use Linq instead of filter table activity to copy the results of your filtering ( if any ) to a new DataTable, like so :

If (
( From results As DataRow In yourDT.AsEnumerable()
Where DateTime.Parse(results.Item(“Date”).ToString) > inputStringDate_Parsed
Select results
).Count > 0 ,
( From results As DataRow In yourDT.AsEnumerable()
Where DateTime.Parse(results.Item(“Date”).ToString) > inputStringDate_Parsed
Select results
).CopyToDataTable(),
New DataTable()
)

Assign this to a resultsDT and change the inputs to your variable names.
If resultsDT.rows.count > 0 that means you found data that matches your criteria ( .Equals or is greater etc).
See attached .xaml and the excel test file.

DateExcelComparisson.xaml (7.7 KB)
TestExcelDate.xlsx (8.4 KB)

1 Like
8

This is my filtering condition @prasath17
filter condition

9

Hi @MarinAlexandru Thank you so much for the clear explanation! Will try this!

10

Please check this…

image
image1535×623 43.5 KB

we can get the filtering count as defensive approach explained by @MarinAlexandru to avoid the error if in case no records matches the filtering condition…

11

Hi @prasath17 @MarinAlexandru , this worked for me but only problem i faced is, it is taking more time for execution when i have bulk data in the excel (10 columns and 2000 rows). Is it possible to reduce the execution time if we use Filter data table activity to store the excel data and write it to another excel file?
Thank you so much for your effort!

12

@AswinSridhar - If you have bulk data then LINq is your only best bet, filter will always take more time when # of rows are more.

So you haven’t tried my solution (which is LINq)?

13

Awesome! Thanks @prasath17, LINQ is a elegant solution!

1 Like
14

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