Filtering a numeric datatable column removes all rows when it shouldn't

[charliefik (Clark Coleman)]

I’m trying to do a very simple filter on a datatable where the value in a column needs to be bigger than a certain value and I want to remove the rest.

When I apply the filter all the rows are removed. It’s driving me nuts as this should be very simple!

Attached is the workflow I am using. I don’t want a working workflow I need to understand why my workflow isn’t working. Maybe it’s the data from the excel file if so that is the format it comes in from a data scrape so if that is the problem I need to understand how to fix the data. If it isn’t the problem I need to understand what I’m doing wrong in the filter activity.

The data is in an excel spreadsheet in the data folder

FilterTest.zip (11.4 KB)

Thanks for any help on this matter. I am pulling my hair out!!!

[prasath_S]

Hi @charliefik

Please give a try,

In filter datatable,

Cdbl("CAP") >= cdbl(dblLowest)

Thanks

[charliefik (Clark Coleman)]

Hi @prasath_S,

Thanks for the suggestion, but I tried that and it creates an error preventing the sequence from running.

I tried with the Cdbl on just the Value side (which is a double anyway) but this doesn’t help and I still end up with 0 rows.

[Mohsin_Inamdar]

HI @charliefik,

Try by applying the below code

filteredDT=yourDT.AsEnumerable.Where(Function(x) Convert.ToDuble(x(“ColumnName”).ToString)>varYourValue).CopyToDataTable

[charliefik (Clark Coleman)]

Hi Mohsin,

I really appreciate the help, which does work (the duble just needs to be double) and is a solution but I really want to know why the filter activity isn’t working so I don’t want to close this until someone can explain why I can’t filter a column of values by a value is it a bug with the filter activity or am I doing something wrong. I’m currently using UiPath.UiAutomation.Activities v21.10.3 and UiPath.System.Activities v21.10.2 and CE Studio 2021.10.3

Many many thanks,

[kl_kl]

Hi, unfortunately, you can’t change the format of the “Column” from the filter data table activity , it takes the default datatype present in the data table. If you want to use filter datatable activity you can add a extra column to your datatable of the required type (double) and populate the values using for loop or linq and then apply filter datatable based on this new column.
Thank you

[charliefik (Clark Coleman)]

Hi @kl_kl,

Thanks for the message.
The Excel file in my example (first post has the project file) is seeing the CAP column as numerical values. So when I do a read range shouldn’t it set that column as numerical or are you saying that whenever you do a read range everything no matter what is brought into the data table as a string? Which seems ridiculous for it to have been built that way.

[charliefik (Clark Coleman)]

You were right! I did a get type and it does look like the values are being stored in the datatable as strings
dtValues.Rows(0)(1).GetType.ToString returned System.String.

[charliefik (Clark Coleman)]

As there is no straight forward activity method for changing the data type of a column once it is already set up in the data table. I’m going to go with Mohsin’s solution which is great (I just thought that the read range would match data types in excel rather than blanket turn everything into a string)

Thanks for all your help everyone.

[charliefik (Clark Coleman)]

Does that mean if you do a build datatable with the column type set as double and read range into that empty table it should work (I thought I tried this and it didn’t help, maybe I did it wrong)