Group by column and add other columns accordingly

Hi i need to add the amount that has same tracking number also add the status .Status will be either rejected or success per tracking number

Input will be like
image

Output should be …
image

Hi @tharaninatarajan1901

Check the below linq expression to sum the amount based on the status.

OutputDT = DT.Clone
OutputDT = DT.AsEnumerable.GroupBy(Function(x)x("Status").ToString).Select(Function(x)OutputDT.Rows.Add(x.First.Item("Status").ToString,x.Sum(Function(d)CDbl(d("Amount").ToString)))).CopyToDatatable

Hope it helps!!

Assign Activity:
dtResult = YourOriginDataTableVar.Clone

Assign Activity:
dtResult =
(From d in YourOriginDataTableVar.asEnumerable
Group d by k=d(“Tracking Number”).toString.Trim into grp=Group
Let s = grp.Sum(Function (x) CInt(x(“Amount”).toString.Trim)
Let ra = new Object(){k, s, grp.First()(“Status”)}
Select r=dtResult.Rows.Add(ra)).CopyToDataTable

EDITED - defensive Amount handling / handling dollar format

Assign Activity:
myCI | DataType: CultureInfo =
System.Globalization.CultureInfo.CreateSpecificCulture("en-US")

dtResult =

(From d In YourOriginDataTableVar.asEnumerable()
Group d By k=d("Tracking Number").toString.Trim Into grp=Group
Let va = grp.Select(Function (a1) a1("Amount")).Where(Function (a2) Not isNothing(a2) AndAlso 
Double.TryParse(a2.toString.Trim, NumberStyles.Any, myCI, nothing))
Let s = va.Sum(Function (x) Double.Parse(x.toString.Trim,NumberStyles.Any, myCI))
Let ra = New Object(){k, s, grp.First()("Status")}
Select r=dtResult.Rows.Add(ra)).CopyToDataTable

For the learnings have a look here:

Assign Statement: -
Left Side:- dtOutput

Right Side:-

(From dt in dtInput.AsEnumerable
Group dt By trackingNumber = dt("Tracking Number") Into Group
Let Amount = Group.Sum(Function(val) Cint(val("Amount"))
Let Status = Group.Select(Function(val) val("Status")).First
Select dtInput.Clone.Rows.Add({trackingNumber,Amount,Status})).CopyToDataTable

Thanks .Getting errors as Conversation from string ‘’‘’ to type ‘int’ is not valid.(I guess it is for amount column )

Thanks .Getting errors as Conversation from string ‘’‘’ to type ‘int’ is not valid.(I guess it is for amount column ).I tried giving it as double also .still the same issue

I given to double already in the linq it self. Its working for me. @tharaninatarajan1901

Hey @tharaninatarajan1901 This solution from @Quenton_Wayne_Rebello should work and if you have any null rows filter it out before implementing this.

Cheers

Thanks .If it null row then i need to consider it as 0.Can you please help me .

its about empty strings in the ammount column

Try this,

(From dt in dtInput.AsEnumerable
Group dt By trackingNumber = dt("Tracking Number") Into Group
Let Amount = Group.Sum(Function(val) Integer.TryParse(val("Amount").ToString,0)
Let Status = Group.Select(Function(val) val("Status")).First
Select dtInput.Clone.Rows.Add({trackingNumber,Amount,Status})).CopyToDataTable

getting this validation error

Hey @tharaninatarajan1901 , Try this

(From dt In dtInput.AsEnumerable
Group By trackingNumber = dt("Tracking Number") Into Group
Let Amount = Group.Sum(Function(val)  If(Integer.TryParse(val("Amount").ToString(), 0), Integer.Parse(val("Amount").ToString()), 0))
Let Status = Group.Select(Function(val) val("Status").ToString).First
Select dtInput.Clone.Rows.Add({trackingNumber,Amount,Status})).CopyToDataTable

find the defensive handling approach above

All the amount is replaced as 0.

  • set a breakpoint on the assign activity (where the LINQ is used)
  • debug and get paused
  • open immediate panel
  • type in: dtData
  • change dtData to the Variable name of your datatable variable

can your share with us the screenshot from the result?, thanks
Understanding the 6 Debugging Panels of UiPath in the easiest way possible! - News / Tutorials - UiPath Community Forum

(From d In in_dtInsightsData.AsEnumerable

Group d By trackingNumber=d(“TRACKING NUMBER”).toString.Trim

Into grp=Group

Let oa = grp.Select(Function (a1) a1(“TOTAL VALUE”)).Where(Function (a2) Not isNothing(a2) AndAlso Int32.TryParse(a2.toString.Trim, Nothing))

Let s = oa.Sum(Function (x) CInt(x.toString.Replace(“$”,“”).Trim))

Let ra = New Object(){trackingNumber,s,grp.First()(“Remarks”)}

Select in_dtInsightsData.Rows.Add(ra)).CopyToDataTable

thanks for the screenshot.
Sure that the sum amount is 0 as values / formata from the above description and datatable are different. We will rewrite the LINQ soon and update you

Preview on the change:

Hey @tharaninatarajan1901, have you tried this