Query to have Top 3 Products from the input excel

Help Studio
, ,
1

Given an Excel sheet with columns “OrderID,” “Product,” and “Quantity,”
Need a LINQ query to find the top 3 products by total quantity sold.

2

Hi @sruthi_I

Try this Linq query:

(From row In yourDataTable.AsEnumerable()
                        Group row By Product = row.Field(Of String)("Product") Into Group
                        Let TotalQuantity = Group.Sum(Function(x) Convert.ToInt32(x.Field(Of String)("Quantity")))
                        Order By TotalQuantity Descending
                        Select yourDataTable.Clone.Rows.Add(OrderID:=String.Empty, Product:=Product, Quantity:=TotalQuantity)).Take(3).CopyToDataTable()

If possible share the excel file with dummy data.

Regards

3

Hi @sruthi_I

You can use the below LinQ Expression to get the top 3 products.

Assign -> topProducts = (From row In dt.AsEnumerable() Group row By Product = row.Field(Of String)("Product") Into Group Order By Group.Sum(Function(r) r.Field(Of Integer)("Quantity")) Descending Select New With {.Product = Product, .TotalQuantity = Group.Sum(Function(r) r.Field(Of Integer)("Quantity"))}).Take(3).CopyToDataTable()

Hope it helps!!

4

@sruthi_I

datatable.AsEnumerable.OrderBy(function(row) row(“Quantity”).ToString).Take(3).CopyToDataTable

Cheers!!

5

Hello @sruthi_I

Sequence Name: TopProductsByQuantity

  1. Assign Activity

    • To: topProducts
    • Value: dataTable.AsEnumerable().GroupBy(Function(row) row.Field(Of String)(“Product”)).Select(Function(group) New With {.Product = group.Key, .TotalQuantity = group.Sum(Function(row) row.Field(Of Integer)(“Quantity”))}).OrderByDescending(Function(result) result.TotalQuantity).Take(3).ToList()

  2. For Each Activity

    • TypeArgument: DataRow

    • Values: topProducts

    • Body:

      a. Log Message

      • Message: Product: " + item.Product + ", Total Quantity: " + item.TotalQuantity.ToString()

  3. Log Message

    • Message: “Top 3 products by total quantity logged.”

Thanks & Cheers!!!

6

Hi @sruthi_I ,

Please try below code and let us know if u need anything .

var result = (from row in excelDataTable.AsEnumerable()
group row by row.Field(“Product”) into grp
select new
{
Product = grp.Key,
TotalQuantitySold = grp.Sum(r => r.Field(“Quantity”))
} into result
orderby result.TotalQuantitySold descending
select result).Take(3);

Happy Automation !

1 Like
7

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