Create new table by comparing two other tables: New table contains items from table1 that were not found in table2

Help
1

Hello,

I have two tables and I need to compare them. I need to build a third table that contains the data in table1 that was not found in table 2. I was able to figure out how to find the ones that match but I can’t figure out how to find the ones that don’t match:

(From da In dtfull.AsEnumerable
Join db In dtpart.AsEnumerable
On da(0).ToString.Trim Equals db(0).ToString.Trim
Select da).CopyToDataTable

Do I have to somehow change the equals to not equals?

Note: both tables only have one column each with no name.

2

Hi
hope this would help you

Cheers @Asanka

3

do you want your third DataTable to have less rows than table 1? Did you look into the activity Join Data Tables?

4

I dont understand this part:

In_DT2_ColName_To_Match.ToString

What do I have to do to this part? The names of my data tables are DTFull and DTPart

5

yah instead of
In_DataTable1 mention as DTFull
and
In_DataTable2 mention as DTPart

where for column name
instead of In_DT2_ColName_To_Match mention the column name from DTPart which you want to compare with DTFull.
and instead of In_DT1_ColName_To_Match mention the columnname from DTFull

Cheers @Asanka

6

what if the columns have no names?

7

we can mention the column index that usually starts from 0 for the first column
based on which we can just mention the column index without double quotes like this

Datatable Out_NonMatched_Data = DTPart.AsEnumerable().Where(function(row) Not DTFull.AsEnumerable().Select(function(r) r.Field(Of String)(columnindex)).Any(function(x) x = row.Field(Of String)(columnindex))).CopyToDataTable()

@Asanka

8

Hey I tried it and got this error:

image
image1153×398 28.2 KB

Workflow looks like this:

image
image848×1159 28.1 KB

Code is:
DTPart.AsEnumerable().Where(function(row) Not DTFull.AsEnumerable().Select(function(r) r.Field(Of String)(0)).Any(function(x) x = row.Field(Of String)(0))).CopyToDataTable()

9

In READ RANGE kindly mention the range as “” and also ensure that those sheets have data in it

@Asanka

10

Added in the quotes and they contain data:

image
image1710×796 45.1 KB

image
image1769×668 42 KB

Still same error. Does it have anything to do with the data being string?

11

any idea why same error?

12

No…how would i use join data tables?

13

This way works but in the opposite fashion. Is there just not a way to change it from equals to NOT equals?

(From da In dtfull.AsEnumerable
Join db In dtpart.AsEnumerable
On da(0).ToString.Trim Equals db(0).ToString.Trim
Select da).CopyToDataTable

14

@Asanka
Have Look on this topic
Unmatched records using LINQ, similar to your question along with Demo xaml as well

As you mentioned finding the Common rows we’re Not the Problem at your so end. So in the xamls you will find on how to use the commmons rows to identify the unmatching rows

15

Ah
Kindly have a view on this
hope its resolved
BlankProcess6.zip (10.0 KB)

Cheers @Asanka

1 Like
16

Hey,

Im trying to understand the code. What does d1 and d2 mean?

17

d1, d2 are like variable names, that are used similar to da, db from your sample from above. More clear?

18

Yeah but where are d1 and d2 defined? This is what I dont understand. What do they represent?

19

Yeah it worked! Thank you @Palaniyappan !!!

But I have questions! I want to become a better coder. Can you please take me through step by step what these two lines of code are actually doing?

(From d1 In DTFull.AsEnumerable
Join d2 In DTPart.AsEnumerable
On d1(0).ToString Equals d2(0).ToString
Select d2).CopyToDatatable

AND

dtfull.AsEnumerable().Except(dtfinal.AsEnumerable(),DataRowComparer.Default).CopyToDataTable()

I kind of get it…but not enough. I want to be the best coder I can! Not just a copy and paste guy.

20

it is representing the datarow of loop, defined in the LINQ statement. and it can be used for later computation it get a variable name, e.g. d1