Multiple Column Compare inside IF condition

Help
1

Hi,

I am having following issue, how to overcome it, I have solved the problem but I think its not in the optimized way.

  1. I am having two datatable.
  2. First, datatable has 20 columns and 10 rows.
  3. Second, datatable has 22 colums extra 2 columns compared to first datatable.
  4. So, now using for each for first datatable inside it another for each for the second datatable has been kept.
  5. Inside second for each which has second datable, using IF statement inside it i have compared each and every columns of second datatable with each and every columns of the first datatable.
    eg: row1(“column1”).tostring.trim.tolower.equals(row(“column1”). tostring.tolower) AND row1(“column2”).tostring.trim.tolower.equals(row(“column2”). tostring.tolower) … and so on

So my condition inside IF statement is too long but it is working without any issues, but i think it is not a efficient way to do. Is there any way to short the condition inside IF statement?

@Palaniyappan

1 Like
2

You can try with linq queries.

The other way is instead of using two for each row activities, use one to loop through first datatable and inside it, use Filter data table activity to check the matching data.

1 Like
3

Yes these solutions is fine for the datatable where we need to compare 2 to 3 columns, but in my case I need to compare 20 columns from the first datatable against another set of 20 tables from the second datatable.

4

@monish06
Use JoinDatatable Activity.

5

Actually i even tried it. But am not getting the Expected answer.

6

What about Filter Datatable Activity?

7

Actually the first datatable has unqiue records, whereas second datatable has 1000 of records with duplicate records, so using first datatable in first for each and second datatable in second for each, so using IF condition in the second for each i will be getting the matched data and appending the result.

8

This approach of nested for each row will compare only the rows in the same order, is this really what you need? And to be honest if that is working, then why change it?

9

Both in linq query and filter data table activities, you can specify the columns you would like to compare. In terms of process cycle, these two approaches will be less time consuming than using two loops.

10

Fine
You method is completely fine though the if condition looks good it will work perfect
But to reduce the time taken for looping we can do one thing (not sure may be there could be even more better option, but I thought looping time can be saved as if condition won’t take much time to execute unless it is nested)

We can use one For each row loop where pass the input with second datatable
Inside that loop use a Assign activity like this

int_count = 0
Where int_count is a variable of type int32 with default value as 0

Then one more assign activity
int_count = dt1.AsEnumerable().Where(Function(a) a.Field(of String)(“columnname1”).ToString.Equals(row(“columnname1”) AND a.Field(of String)(“columnname2”).ToString.Equals(row(“columnname2”) AND …till the column you want to add).ToArray().Count

Now use a if condition like this
int_count > 0

If true it goes to then or else part where we ca. Add the data from row variable to a new datatable we need

Cheers @monish06

2 Likes
11

@monish06
there is a chance to work with some techniques

  • using, take, skip, EqualSequence in combination with the itemArray for a row.
  • dynamic iteration over the column definitions from dt1
    Can you share some sample data, that it can be checked if such an approach would be recommendable.

In general it could look like this:
monish06_V2.xaml (11.0 KB)

About the Join DataTable Activity can you please give some more details on what the output differs?

Thanks

2 Likes
13

Thanks bro

1 Like
14

Sure I give a try and share few more details of it