How to group a datatable?

Help Studio
1

Good forums everyone,

I have a table in a datatable type as seen below. In this table, I will make a calculation for purchases for the same material number. I will calculate the difference between the most current international and the most current domestic purchase. I need to pull and process the international and domestic data of the same material number from this table.
image

As a result, we should have 1 external purchase and 1 internal purchase information for each material number. According to this information, transactions will be made according to the “Amount” and “Currency” information.

For example for material number 1003;

The most up-to-date domestic and international procurement information.

Domestic “Domestic 1003 5/13/2023 TRY 3150”
International: “Abroad 1003 7/13/2023 EUR 6500”

Is there a Linq query so that I have all the data like the data above?

Thank you in advance.

2

A general intro:

1 Like
3

We do have some doubts on the requirement description

Give a try at
dtResult =

(From d in dtData.AsEnumerable()
Group d by k1=d("Place of Purchase").toString.Trim.ToUpper(), k2=d(Material Number").toString.Trim into grp = Group
Let fr = grp.OrderBy(Function (g) CDate(g("Purchase Date").toString.Trim)).Last()
Select r = fr).CopyToDataTable
1 Like
4

Hi,

How about the following sample?

image
image781Ă—430 17.8 KB

dt.AsEnumerable.GroupBy(Function(r) Tuple.Create(r("Place of Purchase").ToString,r("Material Number").ToString )).Select(Function(g) g.OrderBy(Function(r) CDate(r("Purchase Date"))).Last()).CopyToDataTable

Sample20231013-4L.zip (9.3 KB)

Regards,

1 Like
5

Thank you, I can use that. Also, for the same process, I don’t want any data to be written to the table for data that does not have overseas data. Is this possible? For example, if there is no overseas information for Material Number 1005, the domestic information should not be in the table.

Regards,

6

Can you try the following expression?

dt.AsEnumerable.GroupBy(Function(r) r("Material Number").ToString).Where(Function(g) g.Any(Function(r) r("Place of Purchase").ToString="Abroad")).SelectMany(Function(g) g.GroupBy(Function(r2) r2("Place of Purchase").ToString).Select(Function(g2) g2.OrderBy(Function(r) CDate(r("Purchase Date"))).Last())).CopyToDataTable

Sample20231013-4Lv2.zip (8.4 KB)

Regards,

1 Like
7

Thank you again and again, it was very useful. Your time was very valuable to me.

Regards,

1 Like
8

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