Retrieve rows from one table that are in another by matching 2 columns

[Nicolas_Raby]

Hello Everyone,

I would like to retrieve all the rows belonging to a datatable, whose values ​​are found in another datatable.
As an example is worth 1000 words, here an example of what i want

I Want to keep rows From this DT :

Name	Number	Price
F1	200	1
F2	700	2
F3	200	3
F4	700	4

Where the column “Name” and “Number” match the column “ID” and “Number” of this one :

ID	Number	Colour
F2	700	Blue
F3	700	Green
F4	700	Purple

Result :

Name	Number	Price
F2	700	2
F4	700	4

I would like to have an efficient solution (if possible no for each activity, rather with a linq query)

Thanks for your help

[Anil_G (Anil Gorthi)]

@Nicolas_Raby

Welcome to the community

Try this in assign

Requireddt = dt1.AsEnumerable.Where(function(x) dt2.AsEnumerable.Any(function(y) y("Number").ToString.Equals(x("Number").ToString) And y("ID").ToString .Equals(x("Name").ToString))).CopyToDataTable

Cheers

[lrtetala (Lakshman Reddy)]

Hi @Nicolas_Raby

Try this

FinalDT = (From row1 In dt1.AsEnumerable()
              Join row2 In dt2.AsEnumerable()
              On row1.Field(Of String)("Name") Equals row2.Field(Of String)("ID") And
                 row1.Field(Of Integer)("Number") Equals row2.Field(Of Integer)("Number")
              Select row1).CopyToDataTable()

Regards,

[erigert_ndokaj]

Hello @Nicolas_Raby

So you can use LINQ to achieve this, but remember to add an If condition in case the result has no data it will throw an error, so for this create an if to check if the result >0

If the result is true than do the assign,

Here is the LINQ →

(From row1 In dt1.AsEnumerable()
Join row2 In dt2.AsEnumerable()
On row1("Name").ToString() Equals row2("ID").ToString() And row1("Number").ToString() Equals row2("Number").ToString()
Select row1).CopyToDataTable()

Same result can be achieved if you put the assign in a try catch activity.

[vrdabberu (Varunraj Dabberu)]

Hi @Nicolas_Raby

=> Read Range Workbook

Output-> dt1

=> Read Range Workbook

Output-> dt2

=> Use below syntax in Assign:

resultTable = (From row1 In dt1.AsEnumerable()
              Join row2 In dt2.AsEnumerable() On row1.Field(Of String)("Name") Equals row2.Field(Of String)("ID") And row1.Field(Of Double)("Number") Equals row2.Field(Of Double)("Number")
                 Select row1).CopyToDataTable()

=> Write Range Workbook resultTable back to excel

Check the below image for better understnading:

Sequence8.xaml (8.5 KB)

DT1 and DT2 are Input sheets and Output is Output Sheet
DT Excel.xlsx (9.6 KB)

Regards

[Nicolas_Raby]

Thank you all, I have the expected result! Simple curiosity, which is the most efficient method between that of Anil_G and Irtetala and why? Thanks again for the speed!

[Janet_Caroline]

1. In the Assign activity properties, create a new variable to store the result of the LINQ query. Let’s call it joinedTable.
2. In the Assign activity’s To field, assign the LINQ query result to the joinedTable variable.
3. Write the LINQ query within the Assign activity’s expression editor.

Here’s an example of how you can write the LINQ query within the Assign activity:

joinedTable = (From row1 In DT1.AsEnumerable()
Join row2 In DT2.AsEnumerable()
On New With { Key .Name = row1.Field(Of String)(“name”), Key .Number = row1.Field(Of Integer)(“number”) }
Equals New With { Key .Name = row2.Field(Of String)(“ID”), Key .Number = row2.Field(Of Integer)(“number”) }
Select New With
{
.Name = row1.Field(Of String)(“name”),
.Number = row1.Field(Of Integer)(“number”),
.Price = row1.Field(Of Integer)(“price”),
.Color = row2.Field(Of String)(“color”)
}).CopyToDataTable()

or

(From row1 In dt1.AsEnumerable()
Join row2 In dt2.AsEnumerable() On row1.Field(Of String)(“Name”) Equals row2.Field(Of String)(“ID”) And row1.Field(Of Double)(“Number”) Equals row2.Field(Of Double)(“Number”)
Select row1).CopyToDataTable()

if this answers your question please mark the answer as your solution.

[Nicolas_Raby]

I’m trying your solution but i’m in C#, i tried this :

it worked at first when i didn’t have the “&&”, i tried with “and” and not working neither, do you know what’s wrong ?

[vrdabberu (Varunraj Dabberu)]

Hi @Nicolas_Raby

In case of C# the below code should work

=> Read Range Workbook

Output-> dt1

=> Read Range Workbook

Output-> dt2

=> Use below syntax in Assign:

resultDt = (from row1 in dt1.AsEnumerable()
                      join row2 in dt2.AsEnumerable() on new { Name = row1.Field<string>("Name"), Number = row1.Field<double>("Number") }
                                                     equals new { Name = row2.Field<string>("ID"), Number = row2.Field<double>("Number") }
                      select row1).CopyToDataTable();

=> Write Range Workbook resultTable back to excel

Check the below image for better understanding:

Regards

[erigert_ndokaj]

Hello @Nicolas_Raby ,

Let me know if you still need it so I create the code in C#, as that piece of code is in VBA