Filter Data Table Activity - Failing to filter data correctly

Help
1

I have a CSV file that is read into a datatable. The CSV has no headers so the datatable generated generic headers of Column1,Column2,…,…

Here are a couple of the records being read in:
4005,2-I_GWAT,09/02/2020,15-11B,6,888.93,
4005,2-I_GWAT,10/02/2020,15-11B,11,888.93,

I am trying to use the FilterDataTable activity to keep only rows which are >= 200 in Column7 and > 1 in Column5 which both the above examples are.

image
image888×259 8.62 KB

The resulting datatable is empty which I can only assume is because those columns are being recognised as a string rather than a number, so the greater than check doesn’t work.

Any suggestions on how to overcome this issue?

2

@daryldawes Are you sure you have the names of Columns properly, I guesss the Column Names starts from “Column0” When you don’t add headers. Can you Check that and confirm if you are using the right Column :sweat_smile:

3

Thanks. I did check the locals looking at the source datatable and it is the right column numbers. They start at 1 and are consecutive numbers.

Oddly enough, I have just hovered over Column5 and Column7 and it shows the data type as a Double. :expressionless:

image

4

@daryldawes Are there any rows with value greater than 200 :sweat_smile:, Filter Datatable Should Work fine

5

Yes. The 2 records that I posted in my original post have 6 & 11 in Column5 and 888.93 in Column7 which would meet the “keep” conditions provided to the filter datatable activity.

6

@daryldawes Oh if 888.93 was in Column7 , then it should have work, but When i Counted the Column in the post that you gave, it was in Column6 :sweat_smile:

7

@daryldawes Can you try this Query :
DT.AsEnumerable().Where(Function(row) (if(String.IsNullOrEmpty(row(“Column7”).ToString.Trim) Or String.IsNullOrWhiteSpace(row(“Column7”).ToString.Trim),False,Convert.ToDouble(row(“Column7”).ToString.Trim) >= 200)) and (if(String.IsNullOrEmpty(row(“Column7”).ToString.Trim) Or String.IsNullOrWhiteSpace(row(“Column5”).ToString.Trim),False,Convert.ToDouble(row(“Column5”).ToString.Trim) >1))).CopyToDataTable

Assign it to the Same Datatable that you are using

Only if the Filter Datatable is not working properly :sweat_smile:

8

@daryldawes is it solved ?
If not please keep variable type from value column to string as it it comparing string to int. which will alwayd fail.
Compare in this way:
Column name >= Convert.Tostring(200)

Cheers!

9

@7387023380, the issue is still unsolved. I have tried your suggestion and it still doesn’t pick up any records from the datatable.

image

10

@daryldawes Is it Possible to send the csv file?

11

@supermanPunch, this has worked a treat. Wonder why the inbuilt activity doesn’t work but this did. At least I have a solution that I can work with. :slight_smile:

12

@daryldawes You Have to handle Exception for that Statement :sweat_smile: , If there are no rows available after Filtering, The Datatable will be Empty, Hence it will throw an error.

13

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