Find Sum Total Amt

Help Studio
, , , , , , , ,
1

Hello team,

we have to find the sum of column Amt for Id no 180

Its present in below format
Id | Code no| Amt
180 | AN99 |88.66.33,90
180 | AN99 |85.66.33,90
180 | AN99 |84.66.33,90
180 | AN99 |83.66.33,90
180 | AN99 |82.66.33,00
180 | AN99 |81.66.33,90
180 | AN99 |80.66.33,90
180 | AN99 |88.66.33,90
182 | AN99 |88.66.33,90

Things to consider are
1)Only Id 180 has to be considered
For Finding Total Sum of all Amt with Id 180
2)There is a difference in the Format in Which amt is present so would require conversion

Thanks in advance

2

@NISHITHA
To find the sum of the “Amt” column for the “Id” number 180 in UiPath, you can use the following steps:

  1. Read the input data: Use a Read Range activity to read the data from your data source (e.g., an Excel file or a DataTable). Store the data in a DataTable variable, let’s call it “dtInputData”.
  2. Filter the data: Use a Filter Data Table activity to filter the rows based on the “Id” column. Set the filter condition to "Id" = "180". This will create a new DataTable, let’s call it “dtFilteredData”, that contains only the rows with “Id” equal to 180.
  3. Calculate the sum: Use a For Each Row activity to iterate over each row in “dtFilteredData”. Within the loop, perform the following steps: a. Use an Assign activity to convert the “Amt” value to a numeric format. You can use the following expression:

row(“Amt”) = Decimal.Parse(row(“Amt”).ToString().Replace(“.”, “”).Replace(“,”, “.”))

This converts the “Amt” value from the format with commas and periods (e.g., “88.66.33,90”) to a numeric format without commas (e.g., 886633.90).

b. Use an Assign activity to calculate the sum of the “Amt” values. Create a variable, let’s call it “totalAmt”, to store the sum. Increment the “totalAmt” variable with each iteration:

totalAmt = totalAmt + Convert.ToDecimal(row(“Amt”))

  1. Output the sum: Once the loop is complete, you will have the total sum of the “Amt” column for the “Id” number 180 stored in the “totalAmt” variable. You can use a Write Line activity or assign the value to a variable for further processing or display.
3

@NISHITHA

Please try this

Sum = dt.AsEnumerable.Where(function(x) x("ID").ToString.Equals("180")).Sum(function(x) Double.Parse(x("Amt").ToString,System.Globalization.CultureInfo.CreateSpecificCulture("de-DE")))

Sum is of type double

Cheers

4

Thanks @raja.arslankhan for the detailed response!

5

Hi @Anil_G

Thanks for the prompt Response

Tried this method but its giving input string was not in correct format exception.

6

@NISHITHA

Are there spaces…then use .Trim after .Tostring inside the parse method

Or may be can you print the amt column and check if it is coming in correct format already please…

Generally format in excel and what we see in datatable differ

Cheers

7

Hi @NISHITHA ,

First filter datatable with 180
Where you will get data only with 180 then use below linq query.

Use assign activity
int_total=Dt.Asenumerable.Sum(Function(x) if(isnumeric(x(Column name).tostring.trim),convert.toint32(x(Column name).tostring.trim),0)

Thanks,
Suresh.

8

Hi @NISHITHA

Use regex to extract the “Amt” value from the row. The regex pattern can be adjusted based on the format of the “Amt” values in your data.

Dim amtPattern As String = “[0-9]+.[0-9]+.[0-9]+,[0-9]+”
Dim rowAmt As String = System.Text.RegularExpressions.Regex.Match(row(“Amt”).ToString(), amtPattern).Value

Convert the extracted “Amt” value to a decimal. Remove any commas (,) and replace dots (.) with the appropriate decimal separator.

Dim amtString As String = rowAmt.Replace(“,”, “”).Replace(“.”, CultureInfo.CurrentCulture.NumberFormat.CurrencyDecimalSeparator)
Dim amt As Decimal = Decimal.Parse(amtString)

Add the extracted “Amt” value to a running total variable (initialized outside the loop).
totalAmt += amt

Thanks!!

9

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