Check in dt2 if dt1 Column Value occurs - Write Result to dt1

Help Studio
, ,
1

Hi all,

I have 2 data tables - dt1 and dt2
I need to check if column value of dt1 = another column value of dt2. If it is equal, i need to populate some value in one of the column of dt1.

dt1 -

col A col B col C comments
11 aa 121
12 bb 123
13 cc 321
14 dd 432
15 ee 124
16 ff 543
![image 257x141](upload://ctlpFqJnOZcqnWZCwp3Mn5VCVcA.png)

dt2 -

col D col E
1 123
1 321
2 121
3 121
2 123
2 543
![image 129x141](upload://jPv6I7groR28kwwY4moYEzTzeAT.png)

OUTPUT dt1 -

col A col B col C comments
11 aa 121 valid
12 bb 123 valid
13 cc 321 valid
14 dd 432 invalid
15 ee 124 invalid
16 ff 543 valid
![image 265x141](upload://k8Rdo1AKjhYpqzb7u8uVw25bb8M.png)

I need to do it in Linq query. Please suggest

Thanks in advance

1 Like
2

Hey @Smreti_Gupta

Please try using Join Data Table

Type - Left Join

Thanks
#nK

3

Kindly note:
dt1 we got 121
dt2 we got 121 two times

when joining on dt1-ColC to dt2.ColE the inner join will create more rows.

We would recommend to recheck the requirments and to specify what is a match

4

Join will not be a good idea as I have n number of rows in dt1. I want to check if row value of col C of dt1 exist in any of the row value of col E in dt2

5

as mentioned above, we do see the risk of side effects got by using a join

as an alternate we can check with an any statement:
dt2.AsEnumerable.Any(Function (x) x("col E").toString.Trim.Equals(YourSearchTerm))

it will return true if it is found

the statement we can incorporate within:

  • for each row - on dt1 flow
  • custom LINQ
6

Hi @ppr , I tried above query but it’s not giving correct results.

For every row value of dt1, I am getting False value for above query.

7

@Smreti_Gupta
concept is about Any and ItemArray Reconstruction

grafik

(From d1 In dt1.AsEnumerable
Let chk = dt2.AsEnumerable.Any(Function (x) x(1).toString.Trim.Equals(d1(2).toString.Trim))
Let chkMsg = If(chk, "valid", "invalid")
Let ra = d1.ItemArray.Take(3).Append(chkMsg).ToArray
Select r = dtResult.Rows.Add(ra)).CopyToDataTable

grafik

Find starter Help here:
CheckExistenceInRightTable_AnyMethod.xaml (11.0 KB)