hi guys
hope you are doing well ,i have two excel sheet where is one column is common like employee id and i need to compare both column if id will match i need to write “Matched” in third sheet how could I do this ?
![image_2021-02-09_121633|373x243]
Please find the screen shot of my table i have
two table table1,table2 where is id is common field so i need to compare both field if record match i have to take table 2 location field and find in thrid sheet if its match in need to write there Yes else No
From a in dt1.AsEnumerable() Join b in dt2.AsEnumerable() on a(“ID”).ToString Equals b(“ID”).ToString Select dt3.LoadDataRow(new Object(){b.Field(OfString)(“Location”),b.Field(OfString)(“Store Name”),b.Field(OfString)(“Remark”) },False).CopyToDataTable
Hi @Aleem_Khan ,
Try this also,
joinTables.xaml (15.7 KB)
sample input: Input.xlsx (9.0 KB)
test with your input data.
Thanks!
i need to wirte there remark yes or no
@Aleem_Khan
give a try on following:
prepare an empty datatable with the structure of dt3
give a try on following LINQ:
(From d in dt2.AsEnumerable
Let check = dt1.AsEnumerable.Any(Function (x) x(“ID”).toString.Trim.Equals(d(“ID”).toString.Trim)
Let ra = Object(){d(“ID”),check.ToString}
Select dt3.Rows.Add(ra)).CopyToDataTable
instead of yes no it is writing True or false, but this could be adopted within another iteration
As an alternate we would do it with a left join (LINQ, Activity) from dt2 (left side) to dt1(right side) and
- evaluate the ID Col from dt1 within the join result if it is empty (=no) or not(=yes)
dt3 would just be a filter/evaluation result from the dtJoin (the join result datatable)
Thank you so much really appreciate you could you please explain about this function
dt3.Columns(“Post_1”).ColumnName = “Remarks”
dt3.AsEnumerable.Where(Function(x) x(“ID”).ToString.Trim.Equals(x(“ID_1”).ToString.Trim)).ToList.ForEach(Sub(row) row(“Remarks”)= “Yes”)
dt3.AsEnumerable.Where(Function(x) Not x(“ID”).ToString.Trim.Equals(x(“ID_1”).ToString.Trim)).ToList.ForEach(Sub(row) row(“Remarks”)= “no”)
and please help me out where can I learn this type of query i mean how could I use foreach ,if, condition inside the query
dt3.Columns(“Post_1”).ColumnName = “Remarks” → renaming the existing column name. After doing left join we will get few extra columns in our result table. I’m changing this column name “Post_1” to “Remarks”. And deleted unwanted columns using filter datatable.
dt3.AsEnumerable.Where(Function(x) x(“ID”).ToString.Trim.Equals(x(“ID_1”).ToString.Trim)).ToList.ForEach(Sub(row) row(“Remarks”)= “Yes”) → Filtering matching rows between dt1 and dt2 using where condition. Updating “Yes” in remarks column for those filtered rows.
dt3.AsEnumerable.Where(Function(x) Not x(“ID”).ToString.Trim.Equals(x(“ID_1”).ToString.Trim)).ToList.ForEach(Sub(row) row(“Remarks”)= “Yes”) → Filtering non matching rows between dt1 and dt2 using where condition. Updating “No” in remarks column for those filtered rows.
do you have any document to learn this query for uipath i need to use if condition like you use foreach