Check if a row or rows exists in another datatable

Help Activities
, , ,
1

Hi,

What is the easiest way to check if some rows exists in another datatable.

Example:

dt1:
image

After processing the datatable should look like this:
dt2:
image

Sometimes after the processing has finished dt2 can look like this:
image

The “Result” column contains the value of “Bad” but is not from the original datatable(dt1)

I need to check rows that contains the value “Bad” in dt1 so that it doesnt appear again in dt2.
If dt2 has a row that contains the value of “Bad” but isnt exactly the same as in dt1 then it is OK.
But if the rows with the value “Bad” in dt1 also is present in dt2 then I need to handle it, it is not OK.

How can I do this the easiest way?

2

Hi! @atomic ,

You can achieve this by using Filter DataTable

Regards,
NaNi

1 Like
3

Set Operation e.g. Intersect along with datarowComparer.Default

find starter help here:
DataTable_SetOperations.xaml (8.9 KB)

1 Like
4

I will try to play around with this and see how it turns out.
Im currently using datarowComparer.default but will try to implement it with Intersect.

What exactly does intersect do?

5

@ppr I think I found a problem. In your example xaml image

If dtData1 contains two rows that are exactly the same the dt1Common doesnt handle duplicates.
When I tested with the values I have, my dtData1 has 93 rows and dt1Common should also contain 93 rows.

Can Interesect handle duplicates?

6

its how set operations are intended. We will revert back to you soon on this for available alternates. Just give us a littel time. Thanks

1 Like
7

@atomic
we added some test cases can you please check the results and give us feedback:

grafik
grafik368×730 58.1 KB

yellow: bad rows
green - not bad as it’s not matching the bad-bad rule / or only in dtResult present

1 Like
8

@ppr

I think I need to explain again.

dt1:
image
This is a totally valid datatable in my process.

After some actions by the system the datatable that i download(excel) should look like this:
dt2:
image

The rows with the value “Bad” from dt1 needs to be gone in dt2 for the process to continue otherwise I throw an error in my REF process.

Because the process takes up to 15 min to finish and during that time when i download the excelfile again(dt2) it could contain values with “bad” that are totally different from the first datatable(dt1).

The only thing i want to check is if the rows with the “bad” values from dt1 are gone, image

If the rows with the “bad” value in dt1 are gone in dt2 then everything is fine and the process can continue.

Hope this explains it better otherwise I will try again :slight_smile:

9

Just provide the requirements in aformat of less prose text and more rules description format. Thanks. We ill have a look on later.

We are sure that we can help and finalize the case with you.

1 Like
10

@ppr

Thank you, I will try.

Example 1:

image
image660×774 85 KB

Example 2:

image
image652×740 80.9 KB

[Berlin, 15, Bad] = OK, because it was not in dt1.

Hope this is better :smiley:
If not I will try to explain further.

11

Hello,

Try this:
yourDT.AsEnumerable().Any(Function(x) x("Column1").ToString = DesiredValue)
It will return True if any of the rows match the predicate.

12

@ppr I did not add any acitivities in between.
Just an example of how I would like the results.

13

The “DesiredValue” is dynamic im not sure this will do it.

14

Need to iterate it in a loop and then give it in if condition this will resolve the issuue.

As desired value is comming from a loop of table it will be dynamic.
For example
For item in dt1:
In If condition DT2.AsEnumerable().Any(Function(x) x(“Column1”).ToString = item.tostring)
Then codition

Check once

Thanks,
Ravi Gupta

15

Hi,

This only checks 1 column. I need to check the the whole row not just one item in 1 column.

16

@ppr @ravi_gupta

I think I have made something that works.

Example 1(OK):

image
image1449×678 48.1 KB

Example 2(Not OK):

image
image1455×687 51.6 KB

But there is probably a better/other way to do this.

1 Like