LINQ Query to group if the total is less than threshold and group based on descending order using list

Help Activities
, ,
1

Hi Guys,

I have a complex logic to be implemented and I dont want to loop on it.

I have a datatable as shown below.

image
image1367×316 28 KB

  1. I want to fetch all rows that contains the number present in the list i.e. list1-> 900,971
  2. After getting the datarows with given list. I want to group within that list by column Partner,Date,Pinta and only make a group of rows whose threshold is less than 100 and alos should be grouped in descending order.
  3. after grouping the datatrows in descending order. I want to replace that particular group boat no. with highest weight row’s purchase other.

I know its bit confusing so I you can refer the output below.

image
image1364×225 19.8 KB

for reference row no. 2 and 4 are grouped into one because according to descending order this are the first two row which is less than threshold value 100 and for that group 70.29 is highest value in both rows so its corresponding row purchase no is inputted in Boat no and similar goes for row 3 and 5, row 6 and 8, row 7 is grouped individual because its value when descending in row 5,6,7 comes last and when grouped together threshold is more than 100 so only row 6 and 8 is grouped and row 7 is grouped individual, row 9 is not grouped by anyone because its partner column and pinta and date doesnt match with anyone.

@ppr @loginerror

2

please have a crosscheck to your other open topics and lets try to get them closed or consolidated into a clear single question.

Lets have some updates on the requirement description

  • if a term is used, then also define it it detail. e.g. Threshold

sorting criteria definition is to specifiy

in the second table we do see some concatenated values, but cannot find any mention of this within the description.

Thanks for supproting us with a more sharp defined requirement and output description.

3

Hi @ppr ,

thank you for the response

  1. Total sum of a group of weight column should be less than or equal to 100.
    2.order group by weight column since while making a group the highest weight row is preferred first.
  2. the second datatatable is the output the output I should get when I perform the mentioned steps.
5

I am testing below query but it returns rows only when sum of whole group is less than 100 it doesnt split the group accordingly

(From d In dt1.AsEnumerable
Group d By k1= d(“Partner”).toString.Trim,k2 = d(“Date”).toString.Trim,k3 = d(“Pinta”).toString.Trim Into grp=Group
Let c = String.Join(“|”, grp.Select(Function (sd) sd(“sales”).toString).toArray)
Let ra = New Object(){c,k1,k2}
Where grp.Sum(Function(i) Convert.ToDouble(i.Field(Of Double)(“Weight”).ToString)) < 100
Select dtresult.Rows.Add(ra)).CopyToDataTable

6

We would recommend doing the grouping with a LINQ

(From d In dt1.AsEnumerable
Group d By k1= d(“Partner”).toString.Trim,k2 = d(“Date”).toString.Trim,k3 = d(“Pinta”).toString.Trim Into grp=Group
Select g=grp.toList).toList

And process the group and its memeber within the a nested for each
(outer loop = Groups, inner loop the group members)

Here you can realize the group member spliting merging in a more traceable way

7

Hi @ppr,

can you please share the workflow?

8

a little bit short on time. But when you provide a XAML with a build datatable and the sample values it would speed up the things. Thanks

9

sure @ppr

below is xaml and excel with input and output
xaml :
groupinglogic.xaml (12.7 KB)

excel :
testexcel.xlsx (9.9 KB)

11

Here we up with a new prototype:

Doing grouping
Split the groups on threshold or accepts a higher threshold when it is the first group member
creates the rows with concated values… etc

grafik
grafik877×751 38.2 KB

grafik
grafik800×918 65.9 KB

grafik

grafik

Please check for possible bugs, especially on the take highest weight part

Find starter help here:
groupinglogic_V3.xaml (25.7 KB)

12

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