DataTable column value comparison without loop

Help Studio
,
1

Hi,

I have a logical requirement where I need to compare values(int values always) of 6 columns (F to K columns) within same data table. I have to check whether the values are same or if it has difference of 50, then treat them as if they are same. If they are differing with difference of more than 50, then call it out for further validation. So 2 logical requirement.

Is there a way I can compare these values without using loop. There are different validations I need to perform on this data table later, is why I am avoiding looping. Lets say it will always have one row for now (assumption).

Appreciate your assistance with this logic.

image
image712×81 1.99 KB

2

Further to my question… consider 6th value (column K) as base value to compare with other columns(J,I,H,G,F).

The current logic I have is using if condition :
Math.Abs(int32.Parse(io_DT_Last6Period.rows(0)(10).ToString) - int32.Parse(io_DT_Last6Period.rows(0)(5).ToString)) < 50 And
Math.Abs(int32.Parse(io_DT_Last6Period.rows(0)(10).ToString) - int32.Parse(io_DT_Last6Period.rows(0)(6).ToString)) < 50 And
Math.Abs(int32.Parse(io_DT_Last6Period.rows(0)(10).ToString) - int32.Parse(io_DT_Last6Period.rows(0)(7).ToString)) < 50 And
Math.Abs(int32.Parse(io_DT_Last6Period.rows(0)(10).ToString) - int32.Parse(io_DT_Last6Period.rows(0)(8).ToString)) < 50 And
Math.Abs(int32.Parse(io_DT_Last6Period.rows(0)(10).ToString) - int32.Parse(io_DT_Last6Period.rows(0)(9).ToString)) < 50

Not sure if this is efficient solution. So, appreciate any assistance with designing efficient solution.= or alternate suggestions/solution.

Regards,
Harshil

3

The only way to do it is with IF statements. The only thing i’d say to improve the efficiency is to order it by the most likely to fail first, and to use the AndAlso operator instead of And operator, so it will stop checking the rest of the if statement if it evaluates something False

It looks like you have blank values possible as well, so be sure to add some error handling for that (e.g. if string.isnullorempty() Then assign row.item(columnNumber) = 0)

1 Like
4

Thanks Dave. I feel the same… IF statement is the only hope I have for now. I thought of doing nested IF, but it looks too complicated. Anyone new taking over in future might get confused with nested conditions. So, also trying to keep it as simple and possible.

I will update the ‘And’ with ‘AndAlso’ and see how it goes. Didn’t know about this. Thanks! Not that I will notice any difference but surely it would be more efficient once the transactions are processed in bulk.

I am doing the blank check way before it reaches this stage – so all good there :slight_smile:

5

@harshilmehta
Looking to your statement did let me understand that you compared each colF-colK for row 0

Maybe this idea can help, protoyped and explained on a reduced dataset, but dynamizable enough to fit to your case:
grafik

Lets collect all values and order it:
(From x In dtData.Rows(0).ItemArray.Skip(2).Take(4)
Let intV = If(String.IsNullOrEmpty(x.toString),0,CInt(x.toString.Trim))
Order By intV
Select intV).toArray

with Skip and Take we can handle offset and length (can by dynamized, customized as well)
it is handling empty values and orders it

Within assign the min and max value is compaired and we can avoid concated ifs / and compairs
Math.Abs(arrVal.First - arrVal.Last) <= 50

looks like this:
grafik

find demo xaml here:
harshilmehta.xaml (7.3 KB)

6

Thanks for the solution. I like this one!. I will give it a try.