Merge rows and add values of specific columns

Help
1

Hi Guys, I am trying to merge the duplicate row value and add the value of other cell which is not same.

Column1 Column2 Column3 Column4 Column5 HOURS PRICE
7039936 1225 BT CF4N 1 0.10
7039936 1221 CU CBRN 1 0.20
7039936 1221 CU CBRN 1 3.44
7039936 1221 CU CBRN 1 5.46
7039396 8621 MS CGRN 1
7039396 8621 MS CGRN 1 5.75

I am using the below code (which i got it other topic) which filter duplicate and join the particular cell value, however can someone tell me instead of joining cell, how to sum the cell value in the same code.

(From p In dt.AsEnumerable()
Group By x= New With { Key.a =p.Item(“Column0),Key.b=p.Item(“Column1),Key.c=p.Item(“Column2”),Key.d=p.Item(“Column3”),Key.e=p.Item(“Column4”)}
Into Grp = Group Select obj.LoadDataRow (New Object() {grp(0)(0), grp(0)(1), grp(0)(2), grp(0)(3), grp(0)(4),String.Join(””,grp.select(Function(c) c(“HOURS”), c(“PRICE”) ))},False)).CopyToDataTable

The output should be like this

Column1 Column2 Column3 Column4 Column5 HOURS PRICE
7039936 1225 BT CF4N 1 0.10
7039936 1221 CU CBRN 1 0.20 8.9
7039396 8621 MS CGRN 1 5.75

Please someone help with the code.

Regards,
Anto

2

@antoromeo18 Do a group by and Use the Sum aggregate functions on the columns like Hours and Price. Group by Column1 and Column2 , SUM(Hours) and SUM(Price)

3

can you give me the exact syntax please,as I am not good in query language

4

HI @antoromeo18

You can use this component to easily summarize your datatable

https://go.uipath.com/component/datatable-plugins

This contains an activity that will do that for you so that you don’t need to code it :slight_smile:

5

@antoromeo18 You can use this custom activity

6

can you tell me how the input should be in the aggreegatecolumns under data table consolidate?

7

Can someone create a work flow and share it to me please with my sample table?

8

Hey @antoromeo18

Refer the documentation attached to it under resources tab. Its explained with screenshots :slight_smile:

9

@Lahiru.Fernando I tried as per your documentation however getting the below error
image
sample.xlsx (8.5 KB)

Attached sample excel file.

I also tried this code and got Input string not in correct format

(From p In dt.AsEnumerable()
Group By x= New With { Key.a =p.Item(“PO_NUMBER”),Key.b=p.Item(“JOB_NUMBER”),Key.c=p.Item(“DAMAGE”),Key.d=p.Item(“REPAIR_LINE_LOC”),Key.e=p.Item(“REPAIR_QUANTITY”)}
Into Grp = Group Select obj.LoadDataRow (New Object() {grp(0)(0), grp(0)(1), grp(0)(2), grp(0)(4), grp(0)(5), Convert.ToString(Grp.Sum(Function(c) Convert.ToDouble(c.Item(“PART_PRICE”).ToString)))},False)).CopyToDataTable

Please provide your suggestion.

10

HI @antoromeo18

Yes… Firstly, you got the error you mentioned in the screenshot is because you have not specified the aggregation type you want properly. The aggregation type refers to the aggregation function, Sum, Avg, Min, Max etc.

So it should be in a two dimensional array format… which should come like
{({"Hours", "Sum"}), ({"PRICE", "Sum"})}

The second error

is because when aggregating, you cannot have empty values in the aggregating columns. Those should be assigned with a zero or a default value.

I have done a sample workflow for you… Check it out. it works…

DataTablePluginsTest.xaml (7.2 KB)

Let me know how it goes…

If this works, please mark it as the solution too :slight_smile:

2 Likes
11

Its working now, Thank you very much for the solution @Lahiru.Fernando and @WhenCutEsh as well

2 Likes
12

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