Group By And Sum duplicate values and preserve Non- Duplicate Values

[Faraz_Subhani (Faraz)]

Hi,
This might be duplicate topic.

Reference :Identify the Duplicates and sum the value - #6 by ppr

In the above post, there is solution to group by duplicate values and sum but the above solution is removing the non- duplicate values.

Input

Output Required

Issues

1. Using the above reference topic’s solution I am able to get the group by and sum amount of duplicate values, but it is eliminating the non- duplicate values, Test3 in this case. i need that too.

2. For duplicate values summation of integer values is perfect but I also need to concat column(0) and column(1) which has string values and concat is not working.

Any help will be very helpful

[AshwinS2 (Ashwin S)]

hi @Faraz_Subhani

check this

Use dt=Dt.Asenumerable().Sum(row(“columnname”).ToString)

th@nks
@shwin.S

[Lahiru.Fernando]

Hello @Faraz_Subhani

You can easily do this by using this component to group and sum the data as you need…

Use the activity Datatable Consolidate to achieve this. A user manual is also included in the component so that you can easily understand the usage of it…

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

[Faraz_Subhani (Faraz)]

@Lahiru.Fernando how can I use this plugin?

[Lahiru.Fernando]

You can get it installed to you Studio through Manage dependencies option of the Studio.
In that screen, go to Go components and search for it…

[ppr (Peter Preuss)]

@Faraz_Subhani
as an alternate following strategy may could work for you:

• calculcate the distinct AllowanceCategory Values
• iterate over this and write your aggreagations (sum, concatenations) within the group block

Iterate DT For Each Group

@COOLBOT Just a little starter help: Readin the Excel into a DataTable - Read Range e.g. Variablename: dtData - Assumption Column Order is of DataType: Int32 Creating a list of Groups (Distinct Orders) - Assign Activity [grafik] DataType Orders: List(Of Int32) Statement: dtDemo.AsEnumerable.Select(Function (row) row.Field(Of Int32)(“Order”)).Distinct().ToList Iterate over the Groups Filter the original DataTable to all Rows belonging to the Current Group from Iteration - dtFiltered I…

[Faraz_Subhani (Faraz)]

For the aggregate column property I believe I will need to give the columns for which I want the summation to be done.In my case It would be form index 3-8.

[Lahiru.Fernando]

@Faraz_Subhani

For this property, you also need to specify which aggregation command you need. whether its sum, avg, min etc…

So it should be in a format like this

{({"Month1Allowance", "SUM"}), ({"Month2Allowance", "Sum"})}

Change yours to suit this…

[ppr (Peter Preuss)]

@Faraz_Subhani try what it is doing if you remove the round brackets ( )

[Faraz_Subhani (Faraz)]

@Lahiru.Fernando I get the below error

Below is my aggregate column and group by column

[Lahiru.Fernando]

Can you show me the full command in this

@Faraz_Subhani

image.png712×190 8.06 KB

[Faraz_Subhani (Faraz)]

Got it, changed from "SUM’ to "Sum’, it worked, but the output is changing the column names as well.

[Lahiru.Fernando]

yes… it will change the column name to represent the aggregated command…

[Faraz_Subhani (Faraz)]

I am thankful for your prompt responses, one last thing, to concat two string values, as I will have in column index 0 and 1 what should be the aggregate function to be used, I used Concat, but doesn’t work

[Lahiru.Fernando]

Just use something like this…

say you have two string variables named var1 and var2

to concat both… we just need to say var1 + var2… that’s all

if you want to add a separator… it’s like this
var1 + " - " + var2

[Faraz_Subhani (Faraz)]

I meant aggregate function for the property

If you see my first 2 columns have string values that also needs to be concatenated if duplicate found.

[Lahiru.Fernando]

Hmm… that part you will need to do before the aggregations. Not here. In your original datatable create a new column and have the concatinated values. Then use that column in the aggregate just like you use the others

[Faraz_Subhani (Faraz)]

Sure, I will figure out something, Thank you very much @Lahiru.Fernando @ppr @AshwinS2 for your help and support, please keep up the good work.

[ppr (Peter Preuss)]

@Faraz_Subhani
Give me sometime once my train is started i will Look to prepäre some Sample Statements for you

[ppr (Peter Preuss)]

@Faraz_Subhani
here it is:

Lets assume following:
variablename from your origin datatable: dtOrigin
variablename from your aggregated datatable: dtAggregated
dtAggregated has no columns: AllowanceName, AllowanceNature

So after aggregation add a datacolumn for e.g. AllowanceName to dtAggregation

set the ColumnPosition e.g. to first Position (Invoke Method activity)

Position is configured here

TargetObject: dtAggregated.Columns(“AllowanceName”)

then iterate over the dtAggregated and do the concatenation as following

dtOrig.AsEnumerable.Where(function (r) r(“AllowanceCategory”).ToString.Equals(r(“AllowanceCategory”).ToString)).Select(function (r) r(“AllowanceName”).toString).ToArray
use a String Array for DataType

and populate into the corresponding row
String.Join(“,”,arrAllowanceNames)
to: String.Join(“,”,arrAllowanceNames)

Do similiar also for the other column and let us know if it was working