If you can’t get the Filter activity to work, here is an alternative way using .net
filteredRows = dt2.Select.Where(Function(r) r("SALUD").ToString.Trim.ToUpper="EPS010" And If(IsNumeric(r("IBCS").ToString.Trim), CLng(r("IBCS").ToString.Trim), False) ).ToArray
IF activity, condition filteredRows.Count > 0
Assign: Sura = filteredRows.Sum(Function(r) CLng(r("IBCS").ToString.Trim) )
using the filtered rows variable, you can update the rows while keeping the entire data set. However, you can store those filtered rows to a new table with filteredRows.CopyToDataTable inside the IF activity.
I also suggest using CLng() rather than CInt() since the numbers are larger.
If the filtered rows is giving you a .Count of 0, still, then there could be something different with our excel file that we aren’t considering.
That is assigning it as an Array(Of DataRows), so you will need to assign that to that type of variable, not a data table.
The reason you must do this, is because if it filters to ‘no’ datarows, it will throw an exception if you try to use .CopyToDatable (as seen by the previous version of this solution.
If you take a look at my earlier post, it shows how you will need to use an IF activity to check that it found any DataRows. Then, you can do any actions on that array (preferred) or use .CopytoDatable if you wish.
I don’t recommend using a Try/Catch in this method. Normally, it is better to use IF activity to check that the data is valid before calculating.
For example, checking that the filter found any data rows before converting to a datatable or calculating its sum, as shown in my earlier post with an example of assigning the filtered set to an Array(Of DataRows), then using .Count>0 as a condition.
This is just the filter part. I posted 2 versions, one using the Filter Data Table activity and one using .net. So, you have both
Apologies, most of it is in English.
Turns out, the Filter Data Table activity has a problem with matching strings together. Even when I used ‘Starts With’ combined with ‘Ends With’, it still did not work. However, ‘Starts With’ by itself works fine.
Alternatively, you have the .net version as well in case you like it better. For some reason, ‘IsNumeric()’ was causing a problem, so I changed it to ‘Integer.TryParse()’, as you can see in the xaml.
I placed Write Lines to output the values after the calculation for testing. When I ran it using your criteria, I was seeing 56 rows with sum of 176461845.
I stored the file path and criteria for filtering into variables to make it easier for you.
Let us know if you get further errors around this.
Thanks to your help I managed to filter each one of the data I needed, and I managed to do the rest of the percentages and mathematical processes I needed, but this information that I have already filtered, I can modify these formulas so that I can eliminate or not take into account some users’ rows that correspond to a “CEDULA”?