Subtraction of columns in two datatables

Hi,
I have got two data tables, exact same structure, i.e. each data table has 4 columns: 2 String type Columns, 2 Double type Columns)
Now I need to Check the variance from DT1 column 4 and DT2 column 4 (which are of double type) and check if the difference is greater than 0.5

How can I do it? Pls help.

Hello, you can accomplish this using the following logic and using a for each row activity, if activity and assign activities.

ForEach row in DT1
-----variance = Row(3)-DT2.Rows(count)(3)
-----if variance > 0.5 OR variance < -0.5
----------//Your other code here
-----count = count+1

Actually I am clear on the logic part, it’s the syntax that is bothering me.
Can you pls help with that

Also, I need to perform this comparison only when 1st columns are same.,

That is,
if column1 of DT1 = column1 of DT2
if column4 of DT1 - column4 of DT2 > 0.5
then do A else do B

Ok I will make a quick workflow. One thing first, is your datatable in column 4 being stored in type string and need conversion or is it being stored in the double type already?

Already in double type

It willl look like something below:

For Each Row - DT1 (row1)
   For  Each Row - DT2 (row2)
    If row1.Item(0) = row2.Item(0) Then
      If (row1.Item(3) - row2.Item(3)) > 0.5 Then
          Do Some Code ---
          Break - Assuming you are done with that row from DT 2

This will loop through each row in DT 1 and do comparison to all rows in DT2 if first column value matches - If there could be multiple matches from column 1 on datatable 2 then remove the Break.

1 Like

Try using this logic, it compares the columns and if they are the same string and if they are .5 variance then will do you code

1 Like

Syntax (row1.item(3) - row2.item(3)) > 0.5 does not seem to work. I need help with that, provided these are double type values.

try using CDbl() to convert your object to double

1 Like

What error are you receiving, is the datatype of the columns definitely double?

You could try force the conversion
(CDbl(row1.item(3)) - CDbl(row2.item(3)))

1 Like

Ok @TimK and @JosephNehl , I am trying with this conversion first.

Also, nested ifs are better in ‘FOR EACH’ activity or should use a 'flow chart (with flow decisions) as I have few other checks to do as well.

Pls advise. will let you know if my conversion is successful.

Both do the same things, having it as a workflow will look and read better - where possible its best to avoid to many nested Ifs in a sequece and it cant be untidy.

2 Likes

Both work the same, it depends on which make sense to you and your team. Generally, for many nested if statements, flow chart with flow decision is easier to read than if statements.

1 Like

I have tried these statements in flowdecision and getting error as attached. Am I doing something wrong here?

Count is not initialised as it mentions in the error message.

You need to declare it as a variable of type integer.

1 Like

Like TimK stated make sure you create a integer “count” and set the starting value to 0 so that all rows are included

and this count will be incremented each time the loop runs?

Yes, but not automatically, an assign statement is needed after all the logic in your loop has run where

count = count+1

The screenshot I sent in an earlier post gives an example of where to place this inside the loop.

1 Like