LINQ query to group and sum issue

Help Studio
1

Hi,

I need a help to replace if then else command with LINQ query to sum the below data.
I don’t want to use if command because it to slow and involve to many lines of data.

e.g input data

image
image1920×1080 235 KB

expected result

image
image1920×1080 224 KB

Need help and advise on this

Thank you
Regards
Jamuri

2

Hi @Abang_Jamuri_Abang_Shoker

Check with the below query

(From row In DT.AsEnumerable() Group row By INV =New With { Key .INVNum = row.Item(“invno”)} Into INVGroup = Group Select New With {.INVNumber = INV.INVNum, .Sum=INVGroup.Sum(Function(r) Double.Parse(r.Item(“amt”).ToString()))}).ToList

Regards
Gokul

3

Hi,

Hope the following helps you.

img20210917-6

dt.AsEnumerable.GroupBy(Function(r) System.Text.RegularExpressions.Regex.Match(r("invno").ToString,"\d+").Value).Select(Function(g) dt.Clone.LoadDataRow({g.Key,g.Sum(Function(x) Int32.Parse(x("amt").ToString))},False)).CopyToDataTable

Sample20210917-3.zip (14.3 KB)

Regards,

1 Like
4

Hi Gokul thanks for the respond.
I having an error when using your command.

5

Hi Yoichi,

it works !!! …
thank you very much for help. It solve my problem.

Regards
Jamuri

1 Like
6

Hi Yoichi,

I did try ran your solution but if i have value that start with character it will delete the char e.g

invno amt
GM-11 10
GM-11a 10

Result
invno amt
11 20

how can i maintain in front char which expected result is

Result
invno amt
GM-11 20

Regards
Jamuri

7

Hi,

I haven’t test but the following will work probably. Can you try this?

dt.AsEnumerable.GroupBy(Function(r) System.Text.RegularExpressions.Regex.Match(r("invno").ToString,"^\D*\d+").Value).Select(Function(g) dt.Clone.LoadDataRow({g.Key,g.Sum(Function(x) Int32.Parse(x("amt").ToString))},False)).CopyToDataTable

Regards,

1 Like
8

Hi Yoichi,

Yes… the output as expected.
Thank you for your fast respond.

Regards
Jamuri

9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.