Sum of column data

Help Studio
, , , ,
1

Hello Robogen,

I want to sum total “Payment Amount” and Total “Amt in Statement” . And want to write both result just after last row dynamically. Could any expert please kindly help

image
image1920×1080 236 KB

1 Like
2

@Sabbir_Anwar Aslam o alaikum
Please follow these steps.
1-Use 2 assign activity.
2-Calculate sum for different columns by using this->
DT.AsEnumerable.Sum(Function(a)Convert.ToDouble(a(“Column A”).ToString))
3-use add datarow activity and give 2 variables to responding column and others will remain empty
like
{“”,“”,TotalPyment,“”,“”,“”,AmtTotal}

3

Read Range into a datatable then sum the datatable columns.

For how to sum a column, the forum search works great and finds posts like this.

4

Hi @Sabbir_Anwar

How about this expression?

(From d in dtData.AsEnumerable Where Not (isNothing(d("Payment Amount")) OrElse String.IsNullorEmpty(d("Payment Amount").toString.Trim)) Select v = CDbl(d("Payment Amount").toString.Trim)).Sum(Function (x) x)

Regards
Gokul

5

We can do summing up:

Just to name a few options

6

image
image1920×1080 221 KB

7

Check the column name in the excel file @Sabbir_Anwar

Payment Amount

(From d in dtData.AsEnumerable Where Not (isNothing(d(4)) OrElse String.IsNullorEmpty(d(4).toString.Trim)) Select v = CDbl(d(4).toString.Trim)).Sum(Function (x) x)

Amt in Statement

(From d in dtData.AsEnumerable Where Not (isNothing(d(8)) OrElse String.IsNullorEmpty(d(8).toString.Trim)) Select v = CDbl(d(8).toString.Trim)).Sum(Function (x) x)
8

image
image1920×1080 216 KB

9

image
image1920×1080 221 KB

10

Hello @Sabbir_Anwar
Kindly refer to this sample XAML file, you will get some idea
Forum_AddRowValues.zip (76.8 KB)

12

Dtinput.AsEnumerable.Sum(Function(a)Convert.ToDouble(a(“PAYMENT_AMOUNT”).ToString))

image
image1920×1080 223 KB

13

@Sabbir_Anwar give me few moments

14

image
UpdateColumn.xaml (6.1 KB)
Try please this

image
image1003×363 12.6 KB

sum is integer

15

We assume that an assign activity is in use for the Sum LINQ statement
So you can analyze within a debug run the values which are causing issues

  • set a breakpoint on the assign activity
  • debug and get paused
  • open the immediate panel run following statements
  • replace 4 with your column index or column name
dtData.AsEnumerable.Where(Function (x)  Not (isNothing(d(4) OrElse String.IsNullorEmpty(d(4).toString.Trim))).Select(Function (x) x.ItemArray).toList

now we got all null or empty values

when the result is empty then we check for the non parseable values

dtData.AsEnumerable.Where(Function (x)  Not Double.TryParse(d(4).toString.Trim)).Select(Function (x) x.ItemArray).toList

Feel free to share the output with us

Understanding the 6 Debugging Panels of UiPath in the easiest way possible! - News / Tutorials - UiPath Community Forum

16

Hello @Sabbir_Anwar - Can you share your sample input excel

17

P_Amount = DT.Asenumerable.Sum(Function(r) CDbl(“0”+r(“Payment Amount”).Tostring.Trim))

A_Amount = DT.Asenumerable.Sum(Function(r) CDbl(“0”+r(“Amt in Statement”).Tostring.Trim))

Now
D_row=DT.Newrow

D_row(“Payment Amount”)=P_Amount

D_row(“Amt in Statemnet”)=A_Amount

Now Take add data row,
In properties ,Data table as DT and Datarow as D_row

18

Reconcilation_20-Jul_22.xlsx (30.8 KB)

19

image
image1920×1080 204 KB

image
image1920×1080 222 KB

20

@Sabbir_Anwar I think your excel format is different

21

Madam, Could u please kindly help