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

Hi,
This might be duplicate topic.

Reference :Identify the Duplicates and sum the value

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

1 Like

hi @Faraz_Subhani

check this

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

th@nks
@shwin.S

1 Like

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…

2 Likes

@Lahiru.Fernando how can I use this plugin?

1 Like

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…

1 Like

@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
2 Likes

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.

image

1 Like

@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…

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

1 Like

@Lahiru.Fernando I get the below error
image

Below is my aggregate column and group by column

image

1 Like

Can you show me the full command in this

1 Like

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

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

1 Like

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

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 :slight_smile:

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

I meant aggregate function for the property
image

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

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

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.

1 Like

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

@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
grafik

set the ColumnPosition e.g. to first Position (Invoke Method activity)
grafik
Position is configured here


TargetObject: dtAggregated.Columns(“AllowanceName”)

then iterate over the dtAggregated and do the concatenation as following

dtOrig.AsEnumerable.Where(function ® r(“AllowanceCategory”).ToString.Equals(r(“AllowanceCategory”).ToString)).Select(function ® 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

2 Likes