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!!!
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.
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?
Below is DT1 data and the highlighted part is the matching one
Below is DT2 data and the highlighted part is the matching one
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
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.
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
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??
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.
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 for waiting and apologies for late response
I discussed the scenario with the stake holder
and in case we have duplicate items available so we need to pick up the first one as you have mentioned above and update the same.
In the xaml you have shared you have used “For each loop”, Can I use a parallel for each loop instead of a single fir each loop?
Also, is there any other better approach available to take the first item in case of duplicate row items(based on column matching) or the above logic is good to go?
With simple For Each, the execution won’t take much time. I don’t think it would be of any significant use. But, you can use it and do let me know if you find any difference.
better in what sense. You need be objective here. I think the already provided approach is good enough. If you have any ideas we can work on it.
Can you please explain what have you actually performed in the above code you provided.
I was trying to understand that Linq you wrote but was not able to completely understand. It would be really informative if you can explain the code so also I can proceed further.
Yoichi
Anil_G
jast1631
Gokul001
yedukondaluaregala
Jon_Smith
adi.mehare