How to use group by and get sum?

[aapostol]

Hi Everyone,

I need help about my automation problem.

Right now I have the following data below:

As you can see I would like to check if grouping the column Store code and adding sum is possible in UiPath.

Thanks in Advance!

Regards

AA

[Surya_Narayana_Korivipadu]

Hi @aapostol

Welcome to the community.

Follow below steps:

1. Store your input data in a data table called dt1
2. Create one more datable called dt2 where can store output. Build this data table with two columns namely STORE CODE, TOTAL
3. Now use below linq
   (From row in dt1.AsEnumerable
   Group row by sc = row(“STORE CODE”).ToString()
   Let total= grp.sum(Function (x) Convert.ToDouble(x(“CREDIT”).ToString().Replace(“$”,“”).Trim()))
   Let result = New-Object() {grp(0)(“STORE CODE”),total}
   Select dt2.rows.add(result)).CopyToDataTable

[jeevith (Jeevith Hegde, Ph.D.)]

Hi @Surya_Narayana_Korivipadu,

@Surya_Narayana_Korivipadu

(From row in dt1.AsEnumerable
Group row by sc = row(“STORE CODE”).ToString()
Let total= grp.sum(Function (x) Convert.ToDouble(x(“CREDIT”).ToString().Replace(“$”,“”).Trim()))
Let result = New-Object() {grp(0)(“STORE CODE”),total}
Select dt2.rows.add(result)).CopyToDataTable

grp.sum(Function...) I dont see grp being declared. Do you mean to refer sc
Just wondering how would linq know to refer to a keyword grp. Is it a reserved keyword in linq, which we can use when grouping?

Should it not be like below?

(From row in dt1.AsEnumerable
Group row by sc = row(“STORE CODE”).ToString()
Let total= sc.sum(Function (x) Convert.ToDouble(x(“CREDIT”).ToString().Replace("$","").Trim()))
Let result = New-Object() {sc(0)(“STORE CODE”),total}
Select dt2.rows.add(result)).CopyToDataTable

[aapostol]

Hi @Surya_Narayana_Korivipadu

How will I encode the linq code?

I am going to do assign activity?

Please give me a sample thank you in advance!

Regards,

AA

[prasath_S]

Hi @jeevith

From my knowledge, even sc is a temporary variable used to loop through the particular column and group the rows,

We have to assign the grouped rows into a variable by using into grp = Group…now grp (each item will have multiple rows) variable will have the grouped rows

(From row in dt1.AsEnumerable
Group row by sc = row(“STORE CODE”).ToString() into grp = Group
Let total= grp.sum(Function (x) Convert.ToDouble(x(“CREDIT”).ToString().Replace("$","").Trim()))
Let result = New-Object() {grp(0)(“STORE CODE”),total}
Select dt2.rows.add(result)).CopyToDataTable

Thanks

[jeevith (Jeevith Hegde, Ph.D.)]

Hi @prasath_S

That is good to know. So grp has to be declared to be of type Group before we perform operations on it. In into grp = Group I am assuming Group is a reserved word in linq.

@prasath_S

Group row by sc = row(“STORE CODE”).ToString() into grp = Group

I am learning linq only by reading posts in the community, so wanted to know how this particular query works. Thank you for the explanation.

[Surya_Narayana_Korivipadu]

Hi @jeevith

@prasath_S is correct. I forgot to use into grp=Group

[Surya_Narayana_Korivipadu]

Yes. you should use assign activity.

In the left hand side of assign acitivity you should use dt2, in the right hand side you should use Linq.

Also see below updated linq:
(From row in dt1.AsEnumerable
Group row by sc = row(“STORE CODE”).ToString()
Into grp = Group
Let total= grp.sum(Function (x) Convert.ToDouble(x(“CREDIT”).ToString().Replace(“$”,“”).Trim()))
Let result = New-Object() {grp(0)(“STORE CODE”),total}
Select dt2.rows.add(result)).CopyToDataTable

I missed keyword called Into in the previous reply. Thanks @prasath_S for correcting me.