Compare two table and update the value of one table to another table

Help Activities
,
1

Have 2 datable dt1 with id and category and dt2 with id and group columns,

I want to compare IDs of both dt1 and dt2 table and update the dt2 group columns with dt1 category value.

Below is the sample .
Dt1

IMG20240514145129
IMG202405141451291920Ă—2560 840 KB

Dt2

IMG20240514145134
IMG202405141451341920Ă—2560 651 KB

Want to update dt2 column group .

please guide me how I do it using linq query.

2

In general, we can model it with Join Approach or LookUp Approach

is it about overwriting dt2.Group with matching dt1.Category or placing the value within another column?

As it is about value setting we recommend the LookUp Approach (for processing cost balancing)

Assign Actvitiy:
dictLK | Dictionary(Of String, String) =
dt1.AsEnumerable.ToDictionary(Function (x) x(“ID”).toString.Trim,Function (x) x(“Category”).toString.Trim)

The we can use the dictLK within a LINQ / Loop

For each Row in datatable | row in dt2

  • If: dictLK.ContainsKey(row(“ID”).toString.Trim
    • Then: Assign activity - row(“YourColName”) = dictLK(row(“ID”).toString.Trim)
3

Hi @yashashwini2322

=> Read Range Workbook
image
Output → dt1

=> Read Range Workbook
image
Output → dt2

=> Use the below code in Invoke Code:

For Each rowDt1 As DataRow In dt1.Rows
    For Each rowDt2 As DataRow In dt2.Rows
        If rowDt1("ID").ToString() = rowDt2("ID").ToString() Then
            rowDt2("Category") = rowDt1("Category").ToString()
        End If
    Next
Next

Invoke code arguments:

image
image881Ă—253 9.82 KB

=> Write Range Workbook dt2 to same sheet
image

Sequence3.xaml (8.5 KB)
Input.xlsx (9.2 KB)

Regards

4

As an alternate we can do it with the following variation (keep in mind, that we are bound to the column structures)

Assign Actvitiy:
dictLK | Dictionary(Of String, String) =
dt1.AsEnumerable.ToDictionary(Function (x) x(“ID”).toString.Trim,Function (x) x(“Category”).toString.Trim)

Assign Activity:
dt3 = dt2.Clone

Assign Activity:
dt3 | DataTable =

(From d in dt2.AsEnumerable
Let k = d("ID").toString.Trim
Let v = If(dictLK.ContainsKey(k), dictLK(k), d("Group").toString)
Let ra = new Object(){d(0), v}
Select r = dt3.Rows.Add(ra)).CopyToDataTable

when talking about:

we are looking on approaches, which let us do things within the minimal effort compared to others

  • dictLK Creation effort vs. unneeded nested Loops (where needed / unneeded) rows are checked

etc…

5

Thank you it works for me.

1 Like
6

Glad to have helped @yashashwini2322

Happy Automation!!

7

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.