Obtain value from second datatable based on first datatable

Hi Community,

Below is the link of which todays question is a sub problem this was earlier solved by @ppr
Link - LINQ query running for long hours - #40 by Dhruvi_Arumugam
Current problem -

I have 2 datatables, say DT1 and DT2, I want to compare some columns of DT1 to DT2 and if the values match I want to update a new column “DATE” in DT1 with the value from a single column “due date” obtained from DT2 of the matching data set.

For example, columns to be compared in both DT are “Policy name, host name, server name, change” etc. if these columns match in both the DTs i want to pick up the respective " due date" value(present in the same matching row) from DT2 and update it in the “DATE” column of DT1.
For the non matching ones “N/A” is updated.

I have attached a sample excel containing 3 sheets DT1, DT2 and result.
The cells highlighted in green are matching thus the respective value is captured from DT2 and updated in DT1 new column “DATE”.

Yellow ones are the column which are only used for comparison
Sample.xlsx (12.3 KB)

Required solution urgently. Any help is appreciated!!!

@ppr @Yoichi Can you also guide

Thanks!!

Hi @Dhruvi_Arumugam

When you are comparing the data tables based on few columns (highlighted in Yellow color), then how the date will be picked.

Refer the screenshot below

The date should be 02-02-2023 and not 05-01-2023 for the highlighted rows.
If it is what you have provided, then you are considering all the columns while matching.

Or you want the matching to be done on few columns, but while picking the date, all the columns should be matched and the date should be picked from the matched row.
When we match on few columns, there can be more than one rows that are matched, what would be the logic to pick the date date then.

Hi @kumar.varun2

I will check on the above point you raised about the data and revert with the correct file If any mistake is there.

Whereas,

Yes I want the matching to be done on the few columns only as I mentioned, The date to be picked is of that respective row item for example if 2 items match from 2 Datatables (based on few columns) then that row will also have a due date column that column value should be picked.

Yes, this can happen in this can we not just pick up the date mentioned in due date column of that respective matching row items?

Thanks

Hi @Dhruvi_Arumugam

There are total 5 rows which are matching. (first 5 rows)

image

could you confirm.

Hi @kumar.varun2

Below is DT1 data and the highlighted part is the matching one

DT1

Below is DT2 data and the highlighted part is the matching one

image

Highlighted parts are the matching one and Cells D2 and D7 are not matching having opposite values(columns in yellow are considered for comparison)

Below is the Result data set highlighted with red are the matching ones and in the "DATE’ column values are added

image

Now from both the DT1 and DT2 4 rows appear to be matching based on column conditional matching and thus the due date from DT2 data is added in “DATE” column of DT1 and Result is prepared , where there is no matching “N/A” is updated in “DATE” column.

Please let me know of any doubts

Thanks

@Dhruvi_Arumugam

First row of DT1 is also matching based on highlighted columns

Hi @kumar.varun2

Yes, you are right my mistake on that apologies.

In that case we will also consider that as e match and write the respective due date value in the “DATE” column.

Can you please help on this?

Thanks!!

Hi @kumar.varun2

Can you please give your valuable inputs on this, waiting for some help.

Thanks

Hi @Dhruvi_Arumugam

Try this approach.

But as I mentioned earlier also, the logic to pick the date is still not clear.
So, in this approach, if there are more than one row which are matching, then the first row date will be picked.

Give it a try, and once the logic for the date is clear, we can modify the approach

LINQ_DT_ComparingTwoDataTables_v5.xaml (14.9 KB)

Hi @kumar.varun2

Thanks for this , I will try and update you further.

Also,

Currently as per business requirement I have to match rows based on columns I mentioned and then pick that respective row date if match occurs, matching can be one to many as we saw.
By this logic can we not get the expected output like get not getting the first date occured instead getting the date which is in that row.
Is anything missing here??

Please guide.
Thanks

Hi @Dhruvi_Arumugam

Let us take a look at the first row of DT1. Please refer the screenshot

Now, as I mentioned earlier also, when the matching rows (matching is done on few columns) are more than one, in that scenario the logic to pick the date should be clear.

How to locate that particular row?
For this all column (apart from highlighted the other columns also) should be considered? Because then only we can have “that row”

in the example above, for the first of row of DT1, we have 4 matches in DT2 based on the highlighted columns but when we go for the DATE and if we take all the columns for the DATE column, then we cannot find “that row” in DT2.

Hi @kumar.varun2

Thanks for explaining this, I am getting what you are saying. Let me check this logic or the particular scenario from the business, will get back to you today or tomorrow post the inputs I have.

Thanks a lot again.

Thanks