Help on LINQ GroupBy, Max and Min

[Marian_B (Marian)]

Hi,

Need to Group the Id column and get the Min value from the date 1 and the Max value from the Date 2 .

In the Amount 1 column need to get the parallel value of the Date 1 column

In the Amount 2 column need to get the parallel value of the Date 2 column

Sample input file

Input_Output.xlsx (9.3 KB)

@supermanPunch @ppr @Yoichi @kumar.varun2 @ashwin.ashok

[kumar.varun2 (Varun Kumar)]

Hi @Marian_B

Try this

dt_Out=

(
	From row In dt_Input.AsEnumerable()
	Group row By k=row("Id").ToString.Trim
	Into grp=Group
	Let mindr = grp.OrderBy(Function(gr) gr("Date 1")).First()
	Let maxdr = grp.OrderByDescending(Function(gr) gr("Date 2")).First()
	Let d1 = mindr("Date 1")
	Let d2 = maxdr("Date 2")
	Let amt1 = mindr("Amount 1 ")
	Let amt2 = maxdr("Amount 2")
	Select dt_Input.LoadDataRow({k, d1, d2, amt1, amt2}, True)
).CopyToDataTable

Please refer the xaml file

LINQ_GroupByMultipleConditions.xaml (5.6 KB)

[Marian_B (Marian)]

It working @kumar.varun2

Is it possible to replace same thing in this expression?

(From d In DtRead.AsEnumerable
Group d By k=d("Id").toString.Trim Into grp = Group
Let IncMin = String.Join(" ",CDate(grp.Min(Function (n) n("Date 1").toString.Trim)).ToString("dd.MM.yyyy"))
Let SfMax = String.Join(" ",CDate(grp.Max(Function (M) M ("Date 2").ToString.Trim)).ToString("dd.MM.yyyy"))
Let Sa = IncMin(3)
Let Sa1 = IncMin(4)
Let ra = New Object(){k,IncMin,SfMax,Sa,Sa1}
Select r = DtClone.Rows.Add(ra)).CopyToDataTable

[kumar.varun2 (Varun Kumar)]

Do you mean this

(
	From row In dtRead.AsEnumerable()
	Group row By k=row("Id").ToString.Trim
	Into grp=Group
	Let mindr = grp.OrderBy(Function(gr) gr("Date 1")).First()
	Let maxdr = grp.OrderByDescending(Function(gr) gr("Date 2")).First()
	Let d1 = mindr("Date 1")
	Let d2 = maxdr("Date 2")
	Let amt1 = mindr("Amount 1 ")
	Let amt2 = maxdr("Amount 2")
    Let ra = New Object(){k, d1, d2, amt1, amt2}
	Select r = DtClone.Rows.Add(ra)
).CopyToDataTable