Help on LINQ GroupBy, Max and Min

Help Activities
, ,
1

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

image
image1091×273 8.73 KB

Sample input file

Input_Output.xlsx (9.3 KB)

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

2

Hi @Marian_B

Try this

image

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)

1 Like
3

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
4

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
5

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