I am not able to filter on date in a data table

Hi @manjesh_kumar

Try this way around

use if condition with condition as

dt1.AsEnumerable().Where(Function(row) CDate(row(columnname)).toString>Now.ToString).Count<>0

if this condition is True

then

use assign activity to get filtered rows

dt2= dt1.AsEnumerable().Where(Function(row) CDate(row(columnname)).toString>Now.ToString).CopyToDataTable

if the condition is false:

then do accordingly in else section.

Regards,
Nived N

@NIVED_NAMBIAR … Date values cannot be converted to string while comparison. It will not yield the correct result.

Best practice it to use cdate(“String Date value”).date and now.date…for comparison…

But @prasath17
I was also thinking same, but when i saw one post regarding this some months back where dates are compared by string, it is working fine as well

did u trued that way?

Dear @prasath17 ,

An example would be much better for a thick skull like me. :wink:

Regards,
Manjesh

@NIVED_NAMBIAR - This is similar to comparing a INT value as string…

image

Update: Incorrect comparison results while comparing as string…

image

Dear @NIVED_NAMBIAR ,

I checked your suggestion, it is always giving false although there were records which are existing with dates greater than current date.

Regards,
Manjesh

@manjesh_kumar - Please try the .date method.

Dear @prasath17,

I changed it as per your suggestion

dt1.AsEnumerable().Where(Function(row) CDate(row(“Grn Date”)).Date.Equals(now.Date)).Count<>0

however it still returns “false” though there were 2 records which were greater than current date.

Regards,
Manjesh

But you are checking for “equals” not “>” that’s why you are not getting the correct result. Change the comparison to >.

Dear @prasath17

I have tried the suggestion yet the same result.


image

@NIVED_NAMBIAR

Regards,
Manjesh

@manjesh_kumar - Please look closely…

CDate(row(“Grn Date”)).Date. Equals (now.Date))

In the above code, you are checking the Grn Date is equals to Now.date…i believe none of the values in the sheet has the 08/20 date so it is returning the count to 0.

CDate(row(“Grn Date”)).Date > now.Date)

Note: CDate and Datetime.parse will work based on the local system settings, i.e. my local settings MM/dd/yyyy so if use cdate with dd/MM/yyyy format it will thrown an error.

You get it??

Suggestion: Before using LINQ please try the simple for each row and use write Line to print the date values and then based on that use If condition to check you are getting the right results. Once you successfully done this, you can apply the same logic in LINQ query. This way, you will learn how to play with datetime format and datetime parsing etc etc…

1 Like

Hi @manjesh_kumar,

If I were you, my first priority would be to use the incoming data and ensure that I clean or transform the column which I am interested in and save as a new DataTable (TransformedTable).

This ensures that you have one uniform format of input data within UiPath, which does not affect your raw data in excel.

And as @prasath17 said take a sample of your transformed table and use simple for each and DataTable activities. It will not perform well for over 5000 rows when compared to linq. You can do this by slicing the Transformed DataTable

SampleTransformedTable= RawInputTable.Clone   
SampleTransformedTable=TransformedTable.AsEnumerable().Take(50).CopyToDataTable()

SampleTransformedTable= RawInputTable.Clone → This extracts Header Type and Headers names and creates a DataTable named SampleTransformedTable

SampleTransformedTable=TransformedTable.AsEnumerable().Take(50).CopyToDataTable() → This takes top 50 rows from the TransformedTable and copies them to SampleTransformedTable

That is my two cents :slight_smile:

1 Like

Dear @prasath17 ,

Yes I made a terrible mistake, my bad I am absolutely sorry for that and being patient with me.

I corrected the code and now it is running as expected.

Regards,
Manjesh

1 Like

This should be TransformedTable.clone right? Please correct me if I am wrong @jeevith

Hi @prasath17

Yes the idea was to use the same column names as RawInputTable, if the TransformedTable has additional columns after transformation then it is best to use TransformedTable.Clone(). So both should work fine if the column names and types are different.

Good spotting: TransformedTable.Clone() would be the safer choice.

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