Validation_Difference_Condition

[Balachander_Pandian]

Hi Expert,
I have an Excel file with two sheets and i need to validate the sum of total amount columns in both sheets Also, bot will consider to proceed with further workflow if sum of total amounts are comes with difference values of both DTs up to 0.01 to 0.99 (Plus or minus). if difference values go beyond the limit, bot will throw the exception. How can achieve this same LINQ? Here by attached same input
Validation.xlsx (10.1 KB)

[rlgandu (Rajyalakshmi Gandu)]

@Balachander_Pandian

Dim sumSheet1 As Double = dtSheet1.AsEnumerable().Sum(Function(row) Convert.ToDouble(row("Total Amount")))
Dim sumSheet2 As Double = dtSheet2.AsEnumerable().Sum(Function(row) Convert.ToDouble(row("Total Amount")))
difference=Math.Abs(sumSheet1 - sumSheet2)

Take if activity
difference >= 0.01 AndAlso difference <= 0.99 Then
’ Proceed with the further workflow
Else
Throw New Exception(“The difference between the total amounts is beyond the allowed limit.”)

[lrtetala (Lakshman Reddy)]

Hi @Balachander_Pandian

Try this

Math.Abs(DT1.AsEnumerable().Sum(Function(row) row.Field(Of Double)("Total Amount")) - DT2.AsEnumerable().Sum(Function(row) row.Field(Of Double)("Total Amount"))) <= 0.99

Regards,

[vrdabberu (Varunraj Dabberu)]

Hi @Balachander_Pandian

=> Use two Read Range Workbook to read both the sheets Sheet1 and Sheet2 and store both the output in datatables dt1 and dt2.

=> Use the below code in Invoke Code:

' Step 2: Calculate the sum of the "Total Amount" column for each DataTable
Dim sum1 As Double = dt1.AsEnumerable().Sum(Function(row) If(IsNumeric(row("Total Amount").ToString()), Convert.ToDouble(row("Total Amount")), 0))
Dim sum2 As Double = dt2.AsEnumerable().Sum(Function(row) If(IsNumeric(row("Total Amount").ToString()), Convert.ToDouble(row("Total Amount")), 0))

' Step 3: Compare the sums with the specified tolerance
difference = Math.Abs(sum1 - sum2)

Invoke Code Arguments:

=> Use the below condition in If:

If
  difference >= 0.01 AndAlso difference <= 0.99
Then
   WriteLine -> "Sums are within the acceptable range. Proceeding with the workflow."
Else
   Throw Activity -> New Exception("The difference in sums exceeds the acceptable range.")

FLOW:

XAML:
Main.xaml (11.3 KB)

VARIABLE DATATYPE:

Regards

[Balachander_Pandian]

@rlgandu Thank You very much for your kind help on this.