Hello guys,
Here is the following step
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.
Hello @Mr.Bug_Maker
your filter criteria is not clear.
Have you check the Input and output ? @AkshaySandhu
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.
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.
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
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
Regards
Gokul
It not getting the correct value @Gokul001
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}
Cheers
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
Cheers
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
cheers
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
cheers
@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.