Hello ,
I am trying to use filter data table wizard where one of the condition is the Amount column should be always greater than $10 or in other words it should not pick up any amount which is less than 10 dollar. When I am trying to put this filter its not working as expected
@dutta.marina
Instead of using filter datatable use linq query to get appropriate output
Send the sample excel so that it will be easy to provide you the FilterDatatable expression
After read range.
Use an assign activity
And write range
filteredDataTable = dt.AsEnumerable().
Where(Function(row) row.Field(Of Boolean)("Overall Status") = True AndAlso
row.Field(Of String)("Opportunity ID") <> "Oppty" AndAlso
row.Field(Of String)("Stage Name") = "Signed" AndAlso
row.Field(Of String)("RPA Close Eligible") = "True" AndAlso
row.Field(Of String)("Amount") > "$10.00").
CopyToDataTable()
As per screenshot here is how you can use LINQ for filtering this data.
io_dt_TransactionData.AsEnumerable().Where(Function(row) _
row.Field(Of Boolean)("Overall Status") = True AndAlso _
Not String.IsNullOrEmpty(row.Field(Of String)("Opportunity ID (Oppty)")) AndAlso _
row.Field(Of String)("Stage Name") = "Signed" AndAlso _
row.Field(Of Boolean)("RPA Close Eligible") = True AndAlso _
row.Field(Of Decimal)("Amount") > 10.0).CopyToDataTable()
Thanks,
Ashok 
You can try the below linq expression,
- Assign -> dt_output = dt_Input.AsEnumerable().Where(Function(X) CDec(X("Amount").ToString().Split("$"c).Last()) > 10).CopyToDataTable()
Hope it helps!!
First of all check the values in the locals pane if it is coming as integer or a dollar value…if dollar value then you cannot use filter datatable you need to go with linq…if integer then dont with dollar sign and inverted comma directly give 10 in filter datatable
Cheers
getting the cast error
io_dt_TransactionData.AsEnumerable().Where(Function(row) _
row.Field(Of Boolean)(“Overall Status”) = True AndAlso _
Not String.IsNullOrEmpty(row.Field(Of String)(“Opportunity ID (Oppty)”)) AndAlso _
row.Field(Of String)(“Stage Name”) = “Signed” AndAlso _
row.Field(Of Boolean)(“RPA Close Eligible”) = True AndAlso _
row.Field(Of Decimal)(“Amount”) > 10.0).CopyToDataTable()
Check the datatype of your datatable columns and change type of fields accordingly.
Thanks,
Ashok
dt.AsEnumerable().
Where(Function(row) row.Field(Of String)("Overall Status") = "True" AndAlso
row.Field(Of String)("Opportunity ID") <> "" AndAlso
row.Field(Of String)("Stage Name") = "Signed" AndAlso
row.Field(Of String)("RPA Close Eligible") = "True" AndAlso
CDec(row.Field(Of String)("Amount").Split("$"c).Last()) > 10).
CopyToDataTable()
I have tried different methods but getting cast value error for LINQ
Can you share the input file
So that we will see the datatype formats the columns and give an appropriate sol
If I click the Amount cell . I checked by clicking Information Tab in excel and Type itreturned 2 which means its not a number string
@dutta.marina
Okay.If all the columns are of string datatype
Below query will work
filteredDataTable = dt.AsEnumerable().
Where(Function(row) row.Field(Of String)("Overall Status") = "True" AndAlso
row.Field(Of String)("Opportunity ID") <> "Oppty" AndAlso
row.Field(Of String)("Stage Name") = "Signed" AndAlso
row.Field(Of String)("RPA Close Eligible") = "True" AndAlso
row.Field(Of String)("Amount") > "$10.00")
CopyToDataTable()
Yea I made similar changes the running the bot .will update
Okay Sure
I am getting as The source contains no DataRows: Is it because there is no data based on filter conditions
Using > "$10.00" will never work…as said first check the values in locals panel or attach the file here we can check
When you use inverted comma it is a string and you also have $ which again cast cannot convert to integer
Cheers
Attached the excel file . The values are string. The below query is not working for me
io_dt_TransactionData.AsEnumerable().
Where(Function(row) row.Field(Of String)(“Overall Status”) = “True” AndAlso
row.Field(Of String)(“Opportunity ID (Oppty)”) <> “” AndAlso
row.Field(Of String)(“Stage Name”) = “Signed” AndAlso
row.Field(Of String)(“RPA Close Eligible”) = “True” AndAlso
row.Field(Of String)(“Amount”) > “$10.00”).CopyToDataTable
Opportunity Quote.xlsx (628.5 KB)
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.
