To perform sum if column data is matched

Help Studio
, , , ,
1

Hi Team,

I want to perform Sum of the column(“Amount”).

For example - If Column B & E are Matches then Perform sum

In below mentioned excel B2 & B7 Matched so Sum needs to be performed (2640 +500)

Similarly it needs to perform for every item if matches.

image
image1108×331 127 KB

Any suggestions for this scenario.

Thanks in advance

2

Hi @nithya ,

Maybe there is a Clarification to be done,

Also, Is the Output a Single Summed value for the rows which have a matching value in Column B ?

3

The output needs to be stored in a different Excel file.

Below is a screenshot for reference.

If B is matched for particular associate name then sum the amount.

image
image1231×167 41.4 KB

4

@nithya ,

This seems like a Group By case, Could you try with the below Expression using an Assign Activity :

OutputDT = (From d In DT.AsEnumerable
Group d By k=d("Wage Type Text").toString.trim, l=d("Associate Name").toString.trim Into grp=Group
Where grp.Count > 1
Let sumValue = grp.Sum(Function(x)CDbl(x("Amount").ToString))
Let ra = grp.First.ItemArray.Take(grp.First.ItemArray.Count-1).Append(sumValue).ToArray
Select OutputDT.LoadDataRow(ra,False)).CopyToDataTable

Here DT is your input data from the Excel sheet and OutputDT is the Clone of the Input datatable.

Cloning of the OutputDT could be done in the below way :

OutputDT = DT.Clone

This needs to be done before using the above Expression.

For handling errors related to direct CopyToDatatable, you could check the below post :

Let us know if you are not able to get the Expected Output.

5

Thanks @supermanPunch it worked.

1 Like
6

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