Subtraction of columns in two datatables

Help Studio
, ,
1

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.

1 Like
2

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

3

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

4

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

5

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?

6

Already in double type

7

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
8

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

dt
dt1834×1070 35.9 KB

1 Like
9

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.

10

try using CDbl() to convert your object to double

1 Like
11

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
12

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.

13

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
14

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
15

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

error
error864×702 26.2 KB

16

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

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

1 Like
17

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

18

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

19

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