Hello @balupad14, by using your approach I get this error:
Hello @balupad14, by using your approach I get this error:
I have tested your scenario. Yes I am getting the same error. Why ? Because when you read the data from excel to datatable everything is string. So here you are trying to do the sum with string column. That is the reason you are getting this error. If the column is integer , you won’t get this error. you have 2 solution for this.
Solution 1 (More work.)
Solution 2 (Single Drag and drop.)
You can get it from UiPath Go also.
Sample Project : Fer.zip (7.9 KB) (The sample studio version is 2018.4.0)
Can you send me a printscreen of what is presented in the red area?
It is an activity comes from BalaReva.Datatable.Activities. You can know about it from video which is in the above.
Quick question, is it possible to get a partial sum from the values in a DataTable column by filtering by rows?
Case in point, in the below example, i need to calculate sum in ‘Value’ column for all the rows in the ‘Type’ column that contain the string “200.”, “300.”, and so on.
I’m new to linq, managed to use your example above to return the sum of a whole column as Double, but can’t figure out how to insert the Where clause to get what i need.
Any help is highly appreciated, thank you!
EDIT: finally found the answer here - https://forum.uipath.com/t/real-time-business-logic-excel-summation-based-on-rows-using-identifier/56979
And so my working code looks like this:
InputBalanceDT.Select("Type Like '200.*'").CopyToDataTable().AsEnumerable.Sum(Function(x) If(IsNumeric(x(“Value”).ToString.Trim),CDbl(x(“Value”).ToString.Trim),0))
Hi, what I would do it is the following.
This looks nice and simple and to the point, should work. Thank you @Raul8.
I took a deep dive reading up on linq and got a bit stubborn on trying to solve it that way.
I still think linq would be better as a solution because you don’t have to iterate through the whole datatable, you just query it and then apply sum. Mind you, what i posted is just some sample data, the real table will have many more rows.
Yeah for LINQ, I think you got it right.
To use Where, it’s just like the Sum:
InputBalanceDT.AsEnumerable.Where(Function(r) r("Type").ToString.StartsWith("200.") ).ToArray.Sum(Function(x) If(IsNumeric(x(“Value”).ToString.Trim),CDbl(x(“Value”).ToString.Trim),0))
awesome, thank you!
I’m unable to calculate sum from a data table by using SUM function.
Tried with code
“RemoteException wrapping System.Data.DataException: Invalid usage of aggregate function Sum() and Type: Object.”
Please help me on this.
Take a look this will help you.
Does Not Work! As enumerabale is not a member of “system.Data.datattable”
Are you on latest version of Studio and .NET Framework?
Also, please post a snippet of your code so we can make sure you have no typos. Additionally, I have found in the past, that if you delete the
.AsEnumerable and retype it by hand again, it resolves the error.
I’m trying to use your code dt.AsEnumerable.Sum(Function(x) If(IsNumeric(x(“Column2”).ToString.Trim),CDbl(x(“Column2”).ToString.Trim),0))
For some reason when I use this code numbers are chanced from double to whole numbers. E.g. 123,45 is changed to 12345. If format 123.45 is is used then code is working. How can I handle this, I think its some kind of globalization issue?
This is happening because of the cultureinfo. CDbl() and other pre-built conversion methods in VB.NET assume invariant cultureinfo (which is essentially US cultureinfo). It is possible that putting in an assign activity at the top of your code
assign System.Globalization.CultureInfo = new CultureInfo("es-ES") could fix the issue, but I’m not 100% sure on this. NOTE: This is changing it to spain cultureinfo as an example.
A (in my opinion) better way to do it is to update the lambda expression so it is using Double.Parse() instead of CDbl(). Double.Parse allows you to set the cultureinfo in the method directly, so you would change the code to be:
Again, this is for Spain cultureinfo, you should use it to whichever culture-info is applicable to your case by changeing the “es-ES” portion within the parentheses
EDIT: Also, if you are working with monetary amounts, you should be using Decimal instead of Double. Decimal is class that was created specifically for representing monetary amounts as it does not lose any precision
Iv’e used this post to Sum a column in a DT, convert to result to a timespan and used it later on. But I would like to leave out specific numbers in the sum.
Column 0 Column 1 Column 2
Variable X 75,00 Min
Variable Y 30,00 Min
If I would leave out all the Y’s and perhaps other lines in the sum - how would the syntax look like?
As I see the filter syntax it’s locked to only one variable, but in my case I would like to sum several numbers and only leave out one or two variables.
It sounds like you want to only Sum the values that meet a certain criteria?
Check out this previous post of mine which uses the .Where() prior to the Sum:
So you can use a condition like
r("Column 0").ToString.ToUpper.Trim <> "VARIABLE Y" in the Where and it will only sum the value that are not Y. I use .ToUpper so it is not case-sensitive
dt1.AsEnumerable.Where(Function(r) r("Column 0").ToString.ToUpper.Trim <> "VARIABLE Y" ).ToArray.Sum(Function(r) If(IsNumeric(r(“Column 1”).ToString.Trim),Double.Parse(r(“Column 1”).ToString.Trim,CultureInfo("es-ES"),0))
Also refer to @Dave’s post on handling your numbers in different cultures.
Thanks for your time and help, I’m not complete sure I understand the line to the fullest, but it does the trick and the output is as expected.
The line below leaves out my Column “Hjælper” and only sums the rest - all good
Arbejdstid_DT.AsEnumerable.Where(Function(x) x(“column-5”).ToString.Trim <>(“Hjælper”)).Sum(Function(x) If(IsNumeric(x(“Column-6”).ToString.Trim),CDbl(x(“Column-6”).ToString.Trim),0))
Invalid usage of aggregate function Sum() and type: Object