LINQ - Split data

Help Studio
, , , ,
1

Hi,

I want to improve my LINQ skills but I don’t know how to deal with this case:
Let’s say I have this datatable: dtFruits

QTY Fruits
12 Banana
10 Apple
2 Orange

As the output, I want to split these info into 3 sheets in an Excel file with 8 fruits per sheets.
There are several possible “splits” results. What would be the faster and the most efficient?
For example, I could have this:

Sheet 1

QTY Fruits
8 Orange (2), Apple (6)

Sheet 2

QTY Fruits
8 Apple (4), Banana (4))

Sheet 3

QTY Fruits
8 Banana (8)

Should I Create first a dictionary then use a LINQ query with a group?

Thank you

2

@Vandekamp

Assign activity:
   Variable: dtFruitsChunks
   Value: (From row In dtFruits.AsEnumerable()
           Let index = dtFruits.Rows.IndexOf(row)
           Group row By GroupIndex = index \ 8 Into Chunk = Group
           Select Chunk.CopyToDataTable()).ToArray()
1 Like
3

It looks like you a trying to do some packages of fruits where each package has 8 fruits

Alternate Package plan
4 Banana, 3 Apples, 1 Orange
4 Banana, 3 Apples, 1 Orange
4 Banana, 4 Apples

as you mentioned

Have a look here

4

Thanks PPR for the link.

I read the solution provided by @Yoichi regarding the Combination library, but I can’t get the result that I want. I also tried to combine code from my previous topic LINQ: group data and concat, but the result is not ok.

5

As introduced by @Yoichi the origin nature of your request is not a group by

6

Indeed, but my example is incomplete, I’m sorry, what I want from the beginning to the end is:
First I have this dt_AllFruits

Fruits Country QTY
Apple France 4
Apple Spain 8
Orange Italy 14
Orange Portugal 4
Orange Morocco 6
Banana Cameroun 6

If I write this code in an assign activity to a new datatable (dt_FruitsResult)

(From d In dt_AllFruits.AsEnumerable
Group d By k=d("Fruits").toString.Trim Into grp=Group
Let qty = grp.Sum(Function (s) CInt(s("Quantity").toString.Trim))
Let country = String.Join(", ", grp.Select(Function(x) x("Country").ToString.Trim & " (" & x("Quantity").ToString.Trim & ")"))
Let ra = New Object(){grp.First()("Fruits"), qty, country}
Select r = dt_Reporting.Rows.Add(ra)).CopyToDataTable

I will get this table:

Fruits QTY Country
Apple 12 France (4), Spain (8)
Banana 6 Cameroun (6)
Orange 24 Italy (14), Portugal (4), Morocco (6)

But now, I want to split this result by the number of sheets, if I need 3 sheets or 3 datatables, anyway, I will need the Combination library. There a several possible results for the split, but I don’t know how to get this last step

Sheet 1

Fruits QTY Country
Apple 4 France (4)
Banana 2 Cameroun (2)
Orange 8 Morocco (6), Portugal (2)

Sheet 2

Fruits QTY Country
Apple 4 Spain(4)
Banana 2 Cameroun (2)
Orange 8 Portugal (2), Italy (6)

Sheet 3

Fruits QTY Country
Apple 4 Spain (4)
Banana 2 Cameroun (2)
Orange 8 Italy (8)
7

Maybe you can re-read the shared info and given feedback.

As it is more about the combinatoric grouping of subsets we recommend to check also the fundamentals for this.

Kindly note also

And for warm-up gaming have a look here: