Compare DT

Help Activities
, , , , ,
1

Hi everyone!

I have a 2 data table, please refer the below tables

DataTable1,
image

DataTable2,
image

I have to compare the datatable1 with datatable2 and get the “DateIn”

the required output is,

image

Can someone help me with this?

2

One of many options:

Readin in both excels into DataTables, dt1, dt2

Assign Activity
dictLK | DataType: Dictionary(Of String, String) =

(From d in dt2.AsEnumerable
Let n = d("Name").ToString.Split(","c).First().Trim
Let di = d("DateIn").toString.Trim
Select t = Tuple.Create(n,di)).ToDictionary(Function (x) x.Item1,Function (x) x.Item2)

Ensure dt1 has a DateIn Column or add it with Add DataColumn Activity

For each row in DataTable Activity | currentRow in dt1

  • Assign Activity: currentRow("DateIn") = dictLK(currentRow("Name").toString.Trim)
3

Hi @soundarya_A1

You can use the LINQ Expressions to get the required output, Check the below steps,
→ Create a variable called Output_dt which is the datatable datatype variable and write the below expression,

- Assign -> Output_dt = DT1.Clone()

→ After assign activity use the Add data column activity to add the DateIn column to Output_dt.
→ Then use the assign activity and write the below LINQ Expression,

- Assign -> Output_dt = (From row In DT1 
                         Let Name = row("Name").ToString() 
                         Let FirstName = row("First Name").ToString() 
                         Let Datein = If(DT2.AsEnumerable.Any(Function(X) X("Name").ToString().Contains(Name) AndAlso X("Name").ToString().Contains(FirstName)), DT2.AsEnumerable.Where(Function(X) X("Name").ToString().Contains(Name) AndAlso X("Name").ToString().Contains(FirstName)).Select(Function(Y) Y("DateIn").ToString()).FirstOrDefault(), "") 
                         Select Output_dt.Rows.Add({Name, FirstName, Datein}) 
                              	).CopyToDataTable()

→ Then use the write range workbook activity to write the Output_dt to Excel.

Hope it helps!!

4

Check the below workflow for better understanding… @soundarya_A1
Sequence3.xaml (13.8 KB)

Sequence3
Sequence3877×952 71.8 KB

Check the below output,
image

Hope it help!!

5

Hi @soundarya_A1 ,

Find the below output:
image

Linq Query Code:

( From row In dt2.AsEnumerable
Let Name1 = row("Name").ToString.Split(","c).First
Let arrName2 = row("Name").ToString.Split(","c).Skip(1)
From name In arrName2
Let CompareResult = dt1.AsEnumerable.Where(Function(row2) row2("Name").ToString.Trim.ToUpper.Equals(name1.ToUpper.Trim) AndAlso row2("First Name").ToString.Trim.ToUpper.Equals(name.ToUpper.Trim) ).FirstOrDefault
Let result = If(CompareResult Is Nothing, {}, CompareResult.itemArray.concat( {row("DateIn").tostring} ) )
Select dtOutput.Rows.Add(result.toarray)
).copyToDataTable

Mention here for your any doubts.

Attached the xaml.
compareDT.xaml (13.2 KB)

Thanks