Compare 2 columns in 2 datatable and extract value of another column

Help Studio
, ,
1

how to Compare 2 columns in 2 datatable and extract value of another column if any of the column value from dt1 is present in dt2?
I need to extract corresponding row value of datatable dt2 from column X

2

@shilpashree.mohanty,

You should be using Activities - Join Data Tables or LINQ query to join datatables.

Thanks,
Ashok :slight_smile:

3

Yo can use Linq to do . It’s very simple and easy with LINQ. Try once.

4

Can you tell me the linq to this

5

but my requirement is to extract a column value, why should I join dt?

6

@shilpashree.mohanty,

Because you need to compare two DT and then get the result. It’s possible only through Join. Give it a try on your own.

Thanks,
Ashok :slight_smile:

7

// Sample DataTables
DataTable dt1 = new DataTable();
dt1.Columns.Add(“ID”);
dt1.Columns.Add(“Value”);

    DataTable dt2 = new DataTable();
    dt2.Columns.Add("ID");
    dt2.Columns.Add("Value");
    dt2.Columns.Add("X"); // Add the column you want to extract

    // Sample data for demonstration
    dt1.Rows.Add("1", "A");
    dt1.Rows.Add("2", "B");
    dt1.Rows.Add("3", "C");

    dt2.Rows.Add("2", "B", "X1");
    dt2.Rows.Add("3", "C", "X2");
    dt2.Rows.Add("4", "D", "X3");

    // Joining DataTables using LINQ
    var query = from row1 in dt1.AsEnumerable()
                join row2 in dt2.AsEnumerable()
                on row1["Value"] equals row2["Value"]
                select new
                {
                    ValueInDt2 = row2["Value"],
                    ExtractedValue = row2["X"] // Replace "X" with the column name you want to extract
                };

    // Print the extracted values
    foreach (var result in query)
    {
        Console.WriteLine("Value in dt2: " + result.ValueInDt2);
        Console.WriteLine("Extracted value from dt2: " + result.ExtractedValue);
    }
8

Hi @shilpashree.mohanty

you can use the below linq query.

Dim query = From row1 In dataTable1.AsEnumerable()
Join row2 In dataTable2.AsEnumerable() On row1.Field(Of Integer)(“ID”) Equals row2.Field(Of Integer)(“ID”)
Select New With {
.ID = row1.Field(Of Integer)(“ID”),
.Value = row1.Field(Of String)(“Value”),
.ComparisonValue = row2.Field(Of String)(“ComparisonValue”)
}
Hope it helps!! please replace with your data table and column headers. you can use the above code in invoke code activity

9

hey,
we are not allowed to use invoke code

10

can we use 1 assign activity and use enumerable function to get the details

11

@shilpashree.mohanty you need to modify as per your logic, the best why you can do in database level via Query

Option 1
SELECT dt1.*, dt2.X
FROM YourTable1 AS dt1
INNER JOIN YourTable2 AS dt2 ON dt1.MatchingColumn = dt2.MatchingColumn;

Option 2
SELECT dt1.*, dt2.X
FROM YourTable1 AS dt1
WHERE EXISTS (
SELECT 1
FROM YourTable2 AS dt2
WHERE dt1.MatchingColumn = dt2.MatchingColumn
);

12

is this in assign activity?

13

You need to create this SP and you will get the output

14

SP ? what is it ? dint get

15

image
image1073×577 20.6 KB

16

store procedure, invoke code is not allowed
only assign or loops

17

@shilpashree.mohanty
Then did you check the solution given by RobotUi and Baskar_Gurumoorthy
What issue you are getting, also requested to you share the sample file what you have done so its help to us review and provide feedback…

This is just a sample file you need to change the logic according to your columns, pls read about linq, which helps more understanding how to solve the issue.

I hope below will work for you

Maching the record

 var matchingRows = dt2.AsEnumerable()
            .Where(row2 => dt1.AsEnumerable()
                .Any(row1 => row1[col1Name1].Equals(row2[colXName2]) || row1[col2Name1].Equals(row2[colXName2])))
            .CopyToDataTable();

Do the looping on the return value

foreach (DataRow row in matchingRows.Rows)
    {
        Console.WriteLine("Matching row (ID2, X): {0}, {1}", row["ID2"], row["X"]);
    }
18

image
image1027×360 104 KB

19

image
image1422×523 140 KB

@shilpashree.mohanty it looks like you have an error on your code, your query returns boolean value where its expects data table

20

@mukesh.singh
Can you please help here i need a string value as output