Joining DataTable Data on 1 column & Extracting Matching / NonMatching values

I have two Data tables with customer name, Customer No, Customer Add in One Data table and same columns in another Data table i need to save the Details of 6 columns in output Data table if both Data matches then yes in column 7 in output Data table other wise No in column 7.
I need to save the values of both matched and not matched values of both tables.
Can any suggest me possible solution or full outer join linq in UiPath.

Note: if not matched then empty in second 3 columns of output. Example we consider first 3 columns as a group and search in second data table if not found them first 3 columns values exist same next three column values as empty
Thanks in advance

Hi @mettudp075

// Read the two input Days tables into DataTable variables: dtDays1 and dtDays2

// Perform a full outer join using LINQ
Dim query = From row1 In dtDays1.AsEnumerable()
            Join row2 In dtDays2.AsEnumerable()
            On row1.Field(Of String)("Customer No") Equals row2.Field(Of String)("Customer No")
            Into Group
            From row3 In Group.DefaultIfEmpty()
            Select row1.Field(Of String)("Customer Name"),
                   row1.Field(Of String)("Customer No"),
                   row1.Field(Of String)("Customer Add"),
                   If(row3 Is Nothing, "No", "Yes") As MatchStatus,
                   If(row3 Is Nothing, Nothing, row3.Field(Of String)("Customer Name")) As MatchedCustomerName,
                   If(row3 Is Nothing, Nothing, row3.Field(Of String)("Customer Add")) As MatchedCustomerAdd

// Create the output Data table
Dim outputDt As DataTable = New DataTable()
outputDt.Columns.Add("Customer Name", GetType(String))
outputDt.Columns.Add("Customer No", GetType(String))
outputDt.Columns.Add("Customer Add", GetType(String))
outputDt.Columns.Add("Match Status", GetType(String))
outputDt.Columns.Add("Matched Customer Name", GetType(String))
outputDt.Columns.Add("Matched Customer Add", GetType(String))

// Fill the output Data table with the LINQ query results
For Each result In query
    outputDt.Rows.Add(result.CustomerName, result.CustomerNo, result.CustomerAdd, result.MatchStatus, result.MatchedCustomerName, result.MatchedCustomerAdd)

// Now you have the output Data table with the desired columns and data

Hope it helps!!


if possible provide a sample excel input file and expected output so that

we can provide appropriate result

Can we join based on row1(customer no)and row1(customer name)and row1(customer add )= row2(customer no) and row2(customer name) and row2(customer add)

I am attaching the reference sheet if anyone had idea.

Please share your ideas.

sample (1).xlsx (13.1 KB)