How to verify difference of two values is correct or not compared to result value in an Excel Table?

Help Studio
, ,
1

Hi all,

I have this Excel table
image

I need to verify if the difference between A and B columns is equal to C or not except the last row.
Note : No. of rows varies. It is not fixed.

Please help me with the logic
Thanks in Advance :slight_smile:

2
  1. Use Read Range to read the Excel file to a datatable. Check the AddHeaders property.
  2. Use For Each Row in Data Table to loop through the table.
  3. Inside the loop, use If activity to verify that it’s not the last row:
    CurrentRow IsNot dt.AsEnumerable.Last
  4. Inside the first If, add a nested If to compare the difference:
    Cint(CurrentRow("B").ToString.Trim)-Cint(CurrentRow("A").ToString.Trim) = Cint(CurrentRow("C").ToString.Trim)
  5. Use Write Line to print the result.
3

Hi,
Thanks for your response :slight_smile:
Does this code ignore negative values?
Because sometimes 3-5 gives -2 but it should consider as 2 only

4

In that case you will need to use Math.Abs().

Math.Abs(Cint(CurrentRow(“B”).ToString.Trim)-Cint(CurrentRow(“A”).ToString.Trim)) = Cint(CurrentRow(“C”).ToString.Trim)

1 Like
5

Hi,

When I’m using Math.Abs(), It’s throwing error
image

6

One of the cell in your datatable is empty “”. CInt() doesn’t work of empty values. Could you show us your workflow?

7

image
image1352Ă—224 31.8 KB

Usually It’s a Salesforce Application where i need to verify whether the difference is correct or not.
So On every login, the values and the no. of rows changes.
Sometimes it can have empty values also.
Here is the workflow.
CalculateForecstedAttrition-Service.xaml (24.0 KB)

Also I need to extract only rows having + icon
image

8

I can’t open your workflow but if you have empty values you need to decide how to treat them.
E.g. if empty value, assume that it’s 0.

Create three variables A, B, C of type Double and assign them accordingly:

A = CDbl(If (CurrentRow("A").ToString.Trim = "", 0, CurrentRow("A").ToString.Trim))
B = CDbl(If (CurrentRow("B").ToString.Trim = "", 0, CurrentRow("B").ToString.Trim))
C = CDbl(If (CurrentRow("C").ToString.Trim = "", 0, CurrentRow("C").ToString.Trim))

Then change the If statement to compare the difference to:

Math.Abs(B-A) = C