How to look column 1, column 2, Column 3 in Datatable1 to column1 Column 2,column3 in Data Table 2 note Column headers are different

[mettudp075]

I had a scenario where i need to work on two Data tables with customer name, Customer No, Customer Add in One Data table and in another Data table customer_Ass Customer_Contact, Customer_info.

i need to look the values of first 3 column values in first Data Table to the second Data Table.
If Matches save the Details of 6 columns in output Data Table.
if not matched need to sa6the 3 empty values and 3 column values 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 using Linq

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

[mkankatala (Mahesh Kankatala)]

Hi @mettudp075

For look up the values in the two different datatables use the Vlookup activity.

Hope it helps!!

[mettudp075]

I am giving the sample excel file in the output file i need to get the row of 12 columns which consists of input1 6 column and input 6 columns.
sample (1).xlsx (13.1 KB)

[Anil_G (Anil Gorthi)]

@mettudp075

1. Perform a left join on the first table and second table and you would have all the data that is joined together as you need
2. After join simply run the below query and yes and no would be populated

for this use invoke code and pass the joined data as in/out argument

dt.AsEnumerable.ToList.ForEach(sub(r) r("ColumnToBeUpdated") = If(String.IsNullorEmpty(r("SecondTableColumnNameAnyonename").ToString),"No","Yes"))

Hope this helps

cheers

[mettudp075]

By using joins we will lost the data of 2 nd table i need 2nd table not matching rows also. And if matched i need to place the entire row with 12 columns.

Thank you

[Anil_G (Anil Gorthi)]

@mettudp075

Full join will not loose anything…please try

Also try closing all the duplicate topics so that any issue related to topic can be discussed in one place

cheers

[mettudp075]

Hi thank you.

Instead of string is null or empty condition i need to write column 1 = column 5 and column 2= column 6 and column 3 = column 7

[Anil_G (Anil Gorthi)]

@mettudp075

I have given string.IsNullorEmpty because when you do full join…the columns pertaining to second table will be empty only when you dont have a match…so checking for empty should suffice…and when it is not empty it means its matched

May be as you need data from both instead of checking only second table columns you need to check first table column as well…so that in both cases you will get no

dt.AsEnumerable.ToList.ForEach(sub(r) r("ColumnToBeUpdated") = If(String.IsNullorEmpty(r("SecondTableColumnNameAnyonename").ToString) OrElse String.IsNullorEmpty(r("firstTableColumnNameAnyonename").ToString),"No","Yes"))

But if you need only with comprision then use this

dt.AsEnumerable.ToList.ForEach(sub(r) r("ColumnToBeUpdated") = If(r("SecondTableColumnNameAnyonename").ToString.Equals(r("firstTableColumnNameAnyonename").ToString),"Yes","No"))

Also you need not compare all i guess as if atleast one matches then it is a match.But if you need to compare all you can add more conditions r("SecondTableColumnNameAnyonename").ToString.Equals(r("firstTableColumnNameAnyonename").ToString) and r("SecondTableColumnNameAnyonename").ToString.Equals(r("firstTableColumnNameAnyonename").ToString)

Cheers

[mettudp075]

Thank you

I think it will work.

[Anil_G (Anil Gorthi)]

@mettudp075

Glad that it helped…if it works please close the topic and the related topics as well…else happy to help

Cheers

[mettudp075]

Hi By using joins I am not getting appropriate result.

Can you suggest me any linq for if row values in Datatable1 matches with any any row in Data table2 then we need to take entire row1 and row2 values and store in another Data Table.
If Not matched then empty string in row data of data table 1 and values in Data table2
And not matching rows from Data table 1 should come at last with empty rows in Data table 2 part.

Please suggest me if you have any solution.

Thank you

[Anil_G (Anil Gorthi)]

@mettudp075

May i know what issue are you getting with join?

Cheers

[mettudp075]

After write data1 data in output Data table.

It is writing the data from data2 at end of data index.

What i need is it should check row1 in Data table1 in data table 2 if matched then entire row of Row1 and matched row in data table 2 should paste in output data table otherwise row1 data and empty data in row2.

Then remaining row2 data at end.

By using joins I am getting Output like this please refer this img.

Thank you

[Anil_G (Anil Gorthi)]

@mettudp075

Can you please show your code…becasue what you need is what join would do…and as per screenshot I can see no matchign rows are there so the data is like above

cheers

[mettudp075]

Matching rows are there please observe the shared excel sheet. And i need both record in Output.

Thank you.