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
You should be using Activities - Join Data Tables or LINQ query to join datatables.
Thanks,
Ashok
Yo can use Linq to do . It’s very simple and easy with LINQ. Try once.
Can you tell me the linq to this
but my requirement is to extract a column value, why should I join dt?
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
// 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);
}
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
hey,
we are not allowed to use invoke code
can we use 1 assign activity and use enumerable function to get the details
@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
);
is this in assign activity?
You need to create this SP and you will get the output
SP ? what is it ? dint get
store procedure, invoke code is not allowed
only assign or loops
@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"]);
}
@shilpashree.mohanty it looks like you have an error on your code, your query returns boolean value where its expects data table
@mukesh.singh
Can you please help here i need a string value as output