Matching two columns from two datatable

Help Studio
1

Hi folks,

I have two datatable and both datatable have two common columns I want too check from first datatable to second datatable if these two columns match I want to get enrolltime column and update into first datatable column name “morning start ma start date” how I achieve this

For example in first datatable I have columns “SSN”,”planID” “morning star ma start date “ and second datatable “userID”,”plan_Id”,”Enrolltime” I want get enroll time rows and update into morning start ma start date if ssn matches with userid and planID matches with plan_Id
How I. Achieve this ?

2

Hi @Akash_Javalekar1

Your query is quite confusing. Could you be more specific.

If your data doesn’t have the confidential information, do share the Input and expected output data.

3

@Akash_Javalekar1,

You can follow these steps:

  1. Read the DataTables: Use the Read Range activity to read both Excel sheets into two DataTables, dt1 and dt2.
  2. Join the DataTables: Use the Join Data Tables activity to join dt1 and dt2 on the matching columns. However, since Join Data Tables does not directly support columns with different names, you might need to create a new DataTable for joining or use LINQ queries.
  3. Update the First DataTable: Iterate through the joined DataTable to update the “morning start ma start date” column in dt1.

Query to join both datatables:

dtJoined = (From row1 In dt1.AsEnumerable()
              Join row2 In dt2.AsEnumerable()
              On row1("SSN").ToString() Equals row2("userID").ToString() And row1("planID").ToString() Equals row2("plan_Id").ToString()
              Select dt1.Clone().LoadDataRow({row1("SSN"), row1("planID"), row2("Enrolltime")}, False)).CopyToDataTable()

Logic to update dt1:

  1. For Each Row activity to iterate through dt1
  2. Inside the loop, use an If activity to check for matching rows in dtJoined.
  3. If a match is found, update the “morning start ma start date” column in dt1.
For Each Row activity:
  For Each row1 In dt1.AsEnumerable()
    Assign activity:
      matchingRow = dtJoined.AsEnumerable().FirstOrDefault(Function(row) row("SSN").ToString() = row1("SSN").ToString() And row("planID").ToString() = row1("planID").ToString())
      
    If activity:
      Condition: matchingRow IsNot Nothing
      Then: Assign row1("morning start ma start date") = matchingRow("Enrolltime").ToString()

LLM helped me to write this but it’s validated by me.

Thanks,
Ashok :slight_smile:

4

We can model with the help of LookUp Dictionary

Assign activity
dictLK | Dictionary (Of String, String) =

(From d in dt2.AsEnumerable
Let k1 = d("userID").toString.Trim
Let k2 = d("plan_Id").toString.Trim
Let v = d("Enrolltime").toString.Trim
Select t = Tuple.Create(k1 & "_" & k2,v).ToDictionary(Function (x) x.Item1, Function (x) x.Item2)

Then update dt1 within a for each

For each row in DataTable Activity: currentRow in dt1

  • Assign Activity - strKey =
    |currentRow("SSN").toString.Trim & "_" & currentRow("planID").toString.Trim
  • If Activity - Condition:
    | dictLK.ContainsKey(strKey)
    • Then: Assign activity: currentRow(“morning star ma start date”) = dictLK(strKey)