How to group a datatable

Hi everyone,

I have a datatable like this:
Dt

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,

Hi @Betul_Dundar

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:

image

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 .

@Betul_Dundar ,

You can format the value:

.ToString("#,#")

Regards,

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

Hi @Betul_Dundar

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.