How to get matching data from other datatable with columns

[Mansi_Mhatre]

I have 2 datatable, one is main datatable and other is secondary datatable from which 1 column value i want to get into main datatable.

If first check any ‘SenderId’ from main table(Dt1) without T found in ‘CodeId’ from Dt2 then return Status in DT1.
If not SenderId then check ‘RecId’ from Dt1 without T found in ‘CodeId’ from Dt2 then return Status in DT1.
If both no value match then status should be as it is blank.

Output should be:

Note:
There will be more than 30000 data so want solution without looping.

[lakshman (Ganta lakshman)]

@Mansi_Mhatre

1. Use Read Range activity to read the both the tables from Excel file and it will give output as DataTable. Let’s say dtInput1 and dtInput2.

2. And then try below expression to get the required output.

dtOutput = ( From row1 in dtInput1 Join row2 in dtInput2 On row1("Sender ID").ToString.Trim.Equals(row2("CodeId").ToString.Trim.Replace("T","")) Select dtOutput.Rows.Add({row1("SenderID"),row1("RecId"),row1("AccNum"),row2("Status")})).CopyToDataTable

[ppr (Peter Preuss)]

Flow:

LINQ:

(From d In dt1.AsEnumerable
Let cc = {"SenderId","RecId"}.Select(Function (x) d(x).toString.TrimStart("T"c))
Let status = dt2.AsEnumerable.Where(Function (d2) cc.Contains(d2("CodeId").toString.Trim)).Select(Function (e) e("Status").toString).FirstOrDefault()
Let ra = d.ItemArray.Append(status).ToArray
Select r = dt3.Rows.Add(ra)).CopyToDataTable

Worked with a reduced dataset:

Find starter help here:
ppr_LINQBox_Match_Mansi_Mhatre.xaml (12.1 KB)