Group-by and calculate percentage

Hi team

I have a n no. of sap tables in the form of excel from which for particular columns I need to perform group by and present the data in a different sheet along with the percentage wrt the total no. of items in that sheet.

so how can I do it?

thankyou :slight_smile:

@Aishwarya_Bhargava
have a look here for a first introduction

maybe you can share some (also masked data for confidence reasons are fine) sample data, so we can help more specific

The sample input along with the output is attached

the input sheet has the input table from which data of these three columns
MTART MATKL MEINS
needs to be grouped and then percentage needs to be calculated and shown in different sheet
it has been shown for one column using the pivot table

so how can I do this using uipath tool for all three columns and n a no. of tables

sample.xlsx (2.7 MB)

prepare a datatable with build datatable activity - dtReport
configure 3 cols: Label, GroupCount, Percentage

read in your excel with read range - dtData

use an assign acitvity:
left: dtResult
right

(From d in dtData.AsEnumerable
Group d by k1=d("MTART").toString.Trim, k2=d("MATKL").toString.Trim, k3=d("MEINS").toString.Trim into grp=Group
Let p = (grp.Count/dtData.Rows.Count).toString("F2")
Let ra = new Object(){k1, grp.Count, p }
Select r=dtresult.Rows.Add(ra)).CopyToDataTable

this error is coming

sorry was a copy paste error. Please refer to updated post above

this error is coming now

above updated and validated

Assign: Object reference not set to an instance of an object.

Assign is giving this error

is ensured that following was done?

prepare a datatable with build datatable activity - dtReport
configure 3 cols: Label, GroupCount, Percentage

yes i did that, but we have dtresult in the left side of assign, which I think is wrong and also on the right side we are only processing k1 and not k2 and k3

no, groupby is not affected by what we extract.

Can you share your XAML or a screenshot of the relevant implementation parts including variable panel?

test.xaml (7.5 KB)

rename the output from Build Datatable varDTResult
and remove varDtReport from variables

the output is coming wrong i want the three columns to be grouped by separately not togetherly, so when I say “1q2” it should say 10 and along with it the percentage

please share output along with correction description. thanks

maybe you are looking for something like this:
grafik

then we do not group on 3 cols and can do it with:

(From d In varDtData.AsEnumerable
Group d By k1=d("MTART").toString.Trim Into grp=Group
Let p = (grp.Count/varDtData.Rows.Count*100).toString("F2")
Let ra = New Object(){k1, grp.Count, p }
Select r=varDtResult.Rows.Add(ra)).CopyToDataTable
2 Likes

sample.xlsx (12.9 KB)

check the correct output sheet in this, that is the expected output

it is correct but i need for all three columns

Ok thanks for clearification

some of many options:

an immediate result like:

grafik

done by:
grafik

(From x In {"MTART", "MATKL" ,"MEINS"}
From d In varDtData.AsEnumerable
Group d By x=x, k1=d(x).toString.Trim Into grp=Group
Let p = (grp.Count/varDtData.Rows.Count*100).toString("F2")
Let ra = New Object(){x,k1, grp.Count, p }
Select r=varDtResult.Rows.Add(ra)).CopyToDataTable

or

Option: Filter Datatable approach:
prepare a filter config with build Datatable - dtFilterParams
grafik

which can populate with col names and its unique values by:

(From x In {"MTART", "MATKL" ,"MEINS"}
Let dv = varDtData.AsEnumerable.Select(Function (d) d(x).toString.Trim).Distinct().ToList
From y In dv
Let ra = New Object(){x,y}
Select dtFilterParams.Rows.add(ra)).CopyToDataTable

grafik

then you can iterate with a for each row over dtFilterParams and can use the the values for filtering and custom implement the report.

feel free to explore activites and components from marketplace as well. e.g

2 Likes

It worked
thankyou :slight_smile: