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.
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
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
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
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:
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
(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
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
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