Need LINQ expression

Hello guys,

Here is the following step

  • Need to group the ID column in the input file.
  • We should select the larger value from the Last date column and the smaller value from the From Date column.
  • The input should be divided into individual lines based on the Dial/Register column.

For more clarification you can check out the input and output excel file.

Sample file.xlsx (11.9 KB)

Can anyone please help me out on this using LINQ.

@Anil_G @ppr @supermanPunch @Nithinkrishna @Yoichi

Hello @Mr.Bug_Maker
your filter criteria is not clear.

  1. Group by ID - Clear
  2. We should select the larger value from the Last date column and the smaller value from the From Date column this is not clear. Largest value from Last Date is “5/1/2023” and smallest value from From Date is “11/22/2022”. But how to use these value to get record is not defined.
  3. The input should be divided into individual lines based on the Dial/Register column there are multiple value for ID 8956. How to decide which record/value to pick.

Have you check the Input and output ? @AkshaySandhu

  1. It a static condition after Group by need to get larger and smaller value from the date column
  2. You can find the output structure in the excel

Based on Dial/Register column we having 3 types of values EA, ERI & ERC.

In the output also having the Index_Mon_EA, Index_Dem_EA and so on.

In the columns we need to fill tha values.

I have checked the sample output.

  1. In your sample output, for ID 8956 you selected Last Date as “4/3/2023” but largest value from Last date column is “5/1/2023” (as per input given). Note: Past dates are always less than the present and future dates and Present dates are always less than the future dates.

  2. I think (I could be wrong though) you inverted the values for “Index_Mon_ERC” and “Index_Mon_ERI”. Column Index_Mon_ERC should be 0.0000 and Index_Mon_ERI should be 3.0000

It’s my mistake * We should select the smaller value from the Last date column and the larger value from the From Date column.

You are correct @AkshaySandhu

Can you provide me with the LINQ

Hi @Mr.Bug_Maker

Can you try with this LINQ expression?

(From d In DtRemove_Empty.AsEnumerable
Group d By k=d("ID").toString.Trim Into grp = Group
Let LD= grp.OrderByDescending(Function(gr) CDate(gr("Last Date")).ToString("dd-MM-yyyy")).First()
Let FD =  grp.OrderBy(Function(gr) CDate(gr("From date")).ToString("dd-MM-yyyy")).First()
Let LD_1= LD("Last Date")
Let Serie_1 = FD("Type")
Let ra = New Object(){grp.First()(0),grp.First()(2),k,Serie_1 ,CDate(LD_1).ToString("dd-MM-yyyy")}
Select DtBuild.Rows.Add(ra)).CopyToDataTable

Need to update The LINQ for Dial/Register column. Checking on that


It not getting the correct value @Gokul001

@Anil_G @Yoichi , Can you please help me on this?


Please try this

outdt = (From d In DtRemove_Empty.AsEnumerable
Group d By k=d("ID").toString.Trim Into grp = Group
k1 = grp.Max(function(x) Cdate(x("LastDate").ToString))
k2 = grp.Min(function(x) Cdate(x("StartDate").ToString))
k3 = If(grp.where(function(x) x("Dial").ToString.Equals("EA")).Count>0,grp.where(function(x) x("Dial").ToString.Equals("EA"))(0)("old index").ToString,"")
Let ra = New Object(){k,k1,k2,k3}
Select outDT.Rows.Add(ra)).CopyToDataTable

Replicate k3 for each dial/register type and for new index also and add the column as needed here {k,k1,k2,k3}


Thanks for the response.

Its not getting the correct value @Anil_G

After running the above LINQ that you provided

Expected Output is

Need to Look up these Type & Dial/Register columns, based on that we need to take Old index & New Index from the input


do we need to group by with type column as well?

How do you decide which type to consider?

Also i see EA and ERC are as expected only ERI column is having 52 and I believe that is because you used new index instead of old…can you please check


Here is screenshot of the input, Actual result and LINQ query

We need an single line as an output atlast. After we done the Group by TYPE.

Based on Output Header we need to segregation the input file.

If the explanation not good, Please check out the excel file

Sample file.xlsx (12.4 KB)


Can you try this

(From d In dt.AsEnumerable
Group d By k=d("ID").toString.Trim Into grp = Group
Let k1 = grp.Max(Function(x) CDate(x("LastDate").ToString))
Let k2 = grp.Min(Function(x) CDate(x("StartDate").ToString))
Let k3 = If(grp.GroupBy(Function(x) x("Type").ToString)(0).where(Function(x) x("Dial").ToString.Equals("EA")).Count>0,grp.GroupBy(Function(x) x("Type").ToString)(0).where(Function(x) x("Dial").ToString.Equals("EA"))(0)("old index").ToString,"")
Let k4 = If(grp.GroupBy(Function(x) x("Type").ToString)(1).where(Function(x) x("Dial").ToString.Equals("EA")).Count>0,grp.GroupBy(Function(x) x("Type").ToString)(1).where(Function(x) x("Dial").ToString.Equals("EA"))(0)("old index").ToString,"")
Let ra = New Object(){k,k1,k2,k3,k4}
Select dt1.Rows.Add(ra)).CopyToDataTable

Here k3 is for first type of type and EA ,k4 is for second type of type and EA…replicate same for others…so for each dial you will have two one with index 0 and other with index 1…(for each type)

After grouping I am grouping again by type


1 Like

Thank for this LINQ @Anil_G

Current out look like this in the below image

Based on the LINQ expression you are Getting the Index and checking on the DT.

Need to Lookup multiple columns like

Condition 1 → Lookup these column values {Last Date ,Type, Dial/Register} and get the corresponding value from → Old index

Condition 2 → Lookup these column values {Start Date ,Type, Dial/Register} and get the corresponding value from → New index

This the Current LINQ

(From d In DtRemove_Empty.AsEnumerable
Group d By k=d("ID").toString.Trim Into grp = Group
Let Pana_La =  grp.Min(Function(gr) CDate(gr("Last Date").ToString))
Let De_La = grp.max(Function(gr) CDate(gr("Start date").ToString))
Let Serie_De = grp.ToLookup(Function (r) r("Start Date"))(De_La).CopyToDataTable.Rows.Item(0)(8).ToString
Let Serie_Pana = grp.ToLookup(Function (r) r("Last Date"))(Pana_La).CopyToDataTable.Rows.Item(0)(8).ToString
Let Dem_EA = If(grp.GroupBy(Function(x) x("Type").ToString)(0).where(Function(x) x("Dial").ToString.Equals("EA")).Count>0,grp.GroupBy(Function(x) x("Type").ToString)(0).where(Function(x) x("Dial").ToString.Equals("EA"))(0)("Old Index").ToString,"0")
Let Dem_ERC = If(grp.GroupBy(Function(x) x("Type").ToString)(0).where(Function(x) x("Dial").ToString.Equals("ERC")).Count>0,grp.GroupBy(Function(x) x("Type").ToString)(0).where(Function(x) x("Cadran/Registru").ToString.Equals("ERC"))(0)("Old Index").ToString,"0")
Let Dem_ERI = If(grp.GroupBy(Function(x) x("Type").ToString)(0).where(Function(x) x("Dial").ToString.Equals("ERI")).Count>0,grp.GroupBy(Function(x) x("Type").ToString)(0).where(Function(x) x("Cadran/Registru").ToString.Equals("ERI"))(0)("Old Index").ToString,"0")
Let ra = New Object(){grp.First()(0),grp.First()(2),k,Serie_pana,CDate(Pana_La).ToString("dd-MM-yyyy"),Dem_EA,Dem_ERI,Dem_ERC,Serie_De,CDate(De_La).ToString("dd-MM-yyyy"),"","","",grp.First()(13)}
Select DtBuild.Rows.Add(ra)).CopyToDataTable


Can you please elaborate whats wrong in this

because I believe start or end dates are only one for each row…which as per initial request is the min and max of the corresponding columns for each group

Why is the lookup based on last date agin? because lst date what we need is the highest one

And coming to type yes it is being grouped by type and as type is changing for each group the only way we can get the type info is by using index which is 0 and 1 assuming only two types always… which is being implemented already

and coming to dial.Register…yes this is being filtered using where clause because for each group all of these data are needed and that is what is being depicted

Please let me know what is the difference ot what exactly you found out is wrong as I see the output required is same as what we got now…only difference is depending on the group order the order of values are changing. for this we can order grp befor grouping again…this way all are in same order if that works


@Anil_G , I agree with you @Mr.Bug_Maker , Can you please tell us why you’re taking Min for “Last Date” instead of using Max “Last Date” as per your initial requirement

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