Hello
I’d appreciate any help on how to filter out rows that contain PE ratios with a value of 0 and volume higher than average volume. I’ve been browsing the forum and using Chat & Copilot, but I cannot solve this issue. All numbers are doubles. I’ve heard filter data can be used (but it has not worked for me) or an if then statement, but I’m unsure on how this would work.
Best
You can use Filter data table or linQ for this
But here what is Average volume is it a column. You first need to get the Average Volume value and then pass it
Use assign activity to find the average volume
avgVolume = dtInput.AsEnumerable().Average(Function(r) CDbl(r("Volume")))
And then use this LinQ in Assign Activity to filter
dtFiltered = (From row In dtInput.AsEnumerable()
Where CDbl(row("PE")) <> 0 AndAlso CDbl(row("Volume")) > avgVolume
Select row).CopyToDataTable()
Hope this helps!
Sorry to ask (I’m bad at UiPath), but how would this look like in UiPath? I’m trying to choose values where the Volume Column value is higher than the Average Volume Cloumn Value
Columns:
Symbol
PE Ratio
Average
Volume Average
Filter data may not be working because excel column type might be General or Text.
you can change column type to number and try or use below code in Assign activity
isolatedData = deletedData.AsEnumerable().Where(Function(row) CDbl(row("PE Ratio").ToString()) > 0 AndAlso CDbl(row("Volume").ToString()) > CDbl(row("Average Volume").ToString())).CopyToDataTable()
use this before For each row in data table activity
If this helps mark this as a solution so it will be helpful for others.
Basically inside a foreach datatable loop you can check it this way CDbl(CurrentRow("Volume"))>CDbl(CurrentRow("Volume Average"))
Or you can use This linq in Assign activity
dtFiltered = (From row In dtInput.AsEnumerable()
Where CDbl(row("PE")) > 0 AndAlso CDbl(row("Volume")) > CDbl(row("Volume Average"))
Select row).CopyToDataTable()
Out of curiosity, does it not work with CurrentRow.Item()?
@Max_Margittai
Well its same thing, if want to access a value then you should access it based on column name or column index, CurrentRow.Item(0)
What data type would dtFiltered be?
dtFiltered is of type DataTable
I’m getting an error that says DBNull cannot be ‘Double’. How do I fix this error? I’ve uploaded a pic of the code/process
(From row In deleteData.AsEnumerable()
Where CDbl(row(“PE Ratio”)) > 0 AndAlso CDbl(row(“Volume”)) > CDbl(row(“Average Volume”))
Select row).CopyToDataTable()
@Max_Margittai
It is because there is Null values
Try this
dtFiltered = (From row In deleteData.AsEnumerable()
Where Not IsDBNull(row("PE Ratio")) AndAlso Not IsDBNull(row("Volume")) AndAlso Not IsDBNull(row("Volume Average")) _
AndAlso CDbl(row("PE Ratio")) > 0 AndAlso CDbl(row("Volume")) > CDbl(row("Volume Average"))
Select row).CopyToDataTable()
Hope this helps!