Filter Table by countries and rate

Hello, I want to seek on other solution that I can find on this forum. Let’s say we have a datatable and list of values, countries, rate and value. Table example is as below

Countries Rate Value
Malaysia-RICE 0.5 0.5
Malaysia-CHICKEN 0.5 1.2
Malaysia 0.7 3.0
Singapore-BEEF 1.2 2.5
Singapore-NOODLE 1.2 2.5
Indonesia-ROSE 1.0 2.0

I want the output datatable is as below, where it groups the (Group and Rate, where the rate needs to be the same) together and also total up the value. How can I make this process faster when we have whole countries from all over the world?

Countries Rate Value
Malaysia 0.5 1.7
Malaysia 0.7 3.0
Singapore 1.2 5.0
Indonesia 1.0 2.0

Hi,

Hope the following helps you.

img20210909-9

dt.AsEnumerable.GroupBy(Function(r) Tuple.Create(System.Text.RegularExpressions.Regex.Match(r("Countries").ToString,"^[A-Za-z]+").Value,r("rate").ToString)).Select(Function(g) dt.Clone.LoadDataRow({g.Key.Item1,g.Key.Item2,g.Sum(Function(r) Double.Parse(r("Value").ToString))},False)).CopyToDataTable

Sample20210909-2.zip (8.8 KB)

Regards,

1 Like

Thank you so much on this !

1 Like

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