Group by function

[Kiruthiga_Chinnadurai]

Example:

I have supplier column, customer column, quotes created, orders created. Now I have to filter and take sum of number of quotes created, number of orders created for each supplier reseller combination. How to do the group by in UiPath and create chart for supplier based, customer based and supplier-customer combination based.

[Yoichi]

Hi,

Can you share input sample and expected output as file? It’s no problem if dummy data.

Regards,

[Kiruthiga_Chinnadurai]

Input Sample: Input

Output Sample:

[ppr (Peter Preuss)]

[HowTo] Overview on different options for grouping data and processing the groups

This HowTo introduces on the different options for grouping data from a datatable in order to process the grouped data. Introduction Grouping data and processing the grouped data is a common scenario e.g. when the grouped data is to aggregate like summing up, find maximum, get the average or concatening items. Lets have a look on following data: [grafik] A possible scenario could be: Create a report containing following information: the region code the sum of CaseCount per RegionCode th…

[Sagar1 (Sagar Das)]

Hello @Kiruthiga_Chinnadurai ,

Please have a look on the below code if looks expected as per your requirement.

Note: You may remove upper/Trim function if you want.

LINQ Query:

( From row In dtInput.AsEnumerable
Group row By k1= row(“REGION”).ToString.Trim.ToUpper, k2=row(“SUPPLIER”).ToString.ToUpper.Trim, k3= row(“CUSTOMER”).ToString.ToUpper.Trim
Into grp=Group
Let QuoteCount = grp.Where(Function(item) Regex.isMatch(item(“QUOTE NUMBERS”).ToString,strQuotePattern)).Count
Let OrderCount = grp.Where(Function(item) Regex.isMatch(item(“ORDERS CONVERTED”).ToString,strOrderPattern)).Count
Let POCount = grp.Where(Function(item) Regex.isMatch(item(“PO”).ToString,strPOPattern)).Count
Let Result= New Object() {k1,k2,k3,QuoteCount,OrderCount,POCount}
Select DTOpResult.Rows.Add(Result)).CopyToDataTable

GroupByPracticeShared.xaml (17.4 KB)

[vrdabberu (Varunraj Dabberu)]

Hi @Kiruthiga_Chinnadurai

→ Build Data Table

Output-> dtOutput
→ Read Range Workbook
Output-> dtInput
→ Use below syntax in Assign:

Assign -> dtOutput = (From row In dtInput.AsEnumerable()
            Group row By Region = row.Field(Of String)("REGION"),
                              Supplier = row.Field(Of String)("SUPPLIER"),
                              Customer = row.Field(Of String)("CUSTOMER")
            Into Group
            Let Quotes = Group.Count(Function(x) Not IsDBNull(x("QUOTE NUMBERS")))
            Let Orders = Group.Count(Function(x) Not IsDBNull(x("ORDERS CONVERTED")) AndAlso x("ORDERS CONVERTED").ToString() <> "Order Creation Failed")
            Let POs = Group.Count(Function(x) Not IsDBNull(x("PO")) AndAlso x("PO").ToString() <> "PO GENERATION FAILED")
            Select dtOutput.Rows.Add(Region, Supplier, Customer, Quotes, Orders, POs)).CopyToDataTable()

→ Write Range Workbook dtOutput.

Workflow:

Sequence64.xaml (11.2 KB)
Output:
ForumExcel.xlsx (11.1 KB)
Input sheet is the input and Output sheet is the output.

Regards