Hi everyone,
I have a datatable like this:
I want to group if Column0 starts with 760 then, sum of Column1 and return the value. "- " sign is important here.
Hi @Betul_Dundar ,
Use the following expression:
dt.AsEnumerable.Where(function(x) x("Column0").ToString.StartsWith("760")).Sum(Function(x) Cdbl(x("Column1").tostring.Replace("-","")))
If you want to take the numbers having “-” at the end as negative, then the following expression will work:
dt.AsEnumerable.Where(function(x) x("Column0").ToString.StartsWith("760")).Sum(Function(x) if(x("column1").tostring.contains("-"),-1*Cdbl(x("column1").tostring.Replace("-","")),Cdbl(x("column1").tostring)))
Regards,
Hope the Expression works you!
Take Assign:
Leftside: Amount value as integer
Value side:
If(Dt_input.AsEnumerable().Where(Function(row) row("column0").ToString.Contains("760")).Count>0,
CInt(Dt_input.AsEnumerable().Where(Function(row) row("column0").ToString.Contains("760")).Sum(Function (row2) CDbl(row2("Column1").ToString.Replace("-","")))),
0)
Thanks
VP
Hi,
How about the following?
dt = dt.AsEnumerable.GroupBy(Function(r) r("Column0").ToString.Substring(0,3)).Select(Function(g) dt.Clone.LoadDataRow({g.Key,g.Sum(Function(r) Double.Parse(r("Column1").ToString.TrimEnd("-"c),System.Globalization.NumberStyles.Any,System.Globalization.CultureInfo.CreateSpecificCulture("Es-es"))*if(r("Column1").ToString.EndsWith("-"),-1,1)).ToString(System.Globalization.CultureInfo.CreateSpecificCulture("ES-es")) },False)).CopyToDataTable
Output is as follows:
Is this what you expect?
Sample
Sample20230503-3aL.zip (3.0 KB)
Regards.
Hi @vishal.kp ,
Second expression worked but comma is important it gives this value: 93375
Expected: 933,75
Hi @Yoichi ,
Thanks for fast solution. I want to Column0 starts with 760 then, sum of column1 value. Not a datatable. I want to see 933,75 this value type of double.
Hi @Vishnuraj_Pandiyarajan2 , thanks for solution. I need amountvalue as double .
Hi,
How about the following?
This returns Europian style numeric string.
dt.AsEnumerable.Where(Function(r) r("Column0").ToString.Substring(0,3)="760").Sum(Function(r) Double.Parse(r("Column1").ToString.TrimEnd("-"c),System.Globalization.NumberStyles.Any,System.Globalization.CultureInfo.CreateSpecificCulture("Es-es"))*if(r("Column1").ToString.EndsWith("-"),-1,1)).ToString(System.Globalization.CultureInfo.CreateSpecificCulture("ES-es"))
OR
This returns Double type
dt.AsEnumerable.Where(Function(r) r("Column0").ToString.Substring(0,3)="760").Sum(Function(r) Double.Parse(r("Column1").ToString.TrimEnd("-"c),System.Globalization.NumberStyles.Any,System.Globalization.CultureInfo.CreateSpecificCulture("Es-es"))*if(r("Column1").ToString.EndsWith("-"),-1,1))
Sample20230503-3aLv2.zip (2.9 KB)
Regards,
Double type is worked! Thanks
1 Like
Sound good!
Hope u get a solution from Master @Yoichi
In the case : Just change the expression where u used Cint to Cdbl
and the stored value is system.double
Thanks
VP
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.