Compare Datatables - Matching Rows and Columns by Id

Question

I am comparing two datables and I get a 2 Datatable i need to match Columns and Row by D_ID and find the difference between the two values (columns are of string type)
I want to loop through table1 D_ID and find the same in table2 and then loop through column (match)and find the difference

Table 1

D_ID	Split	Value_1	Value_2	Value_3
N6K_12	Str	1.503	9.5E-03	-0.48
N6K_13	Ref	1.680	6.5E-03	-0.63
N6K_14	St	1.690	3.3E-03	-0.43
N6K_15	Ref	1.903	4.5E-03	-0.83

table 2 - column D_ID is not in same order as table1

D_ID	Value_2	Value_1	Value_3
N6K_14	3.503	2.5E-03	-3.48
N6K_13	5.680	2.5E-03	-1.63
N6K_15	4.690	5.3E-03	-0.63
N6K_12	8.903	5.5E-03	-4.83

Output table

D_ID	Split	Value_1	Value_2	Value_3
N6K_12	Str	(1.503)- ( 5.5E-03)		(-0.48)-( -4.83)
N6K_13	Ref	(1.680)-( 2.5E-03)
N6K_14	St	(1.690)-( 2.5E-03)
N6K_15	Ref			(-0.83)-( -0.63)

Vulpes · Answer

Assuming there are no nulls in either datatable and that there will always be a match in table2 for each D_ID value and column name in table1 try this (untested): DataTable table3 = table1.Copy(); foreach(DataRow row in table3.Rows) { string D_ID = row[D_ID].ToString(); foreach(DataRow row2 in table2.Rows) { if (row2[D_ID].ToString() == D_ID) { foreach(DataColumn col in table3.Columns) { if (col.ColumnName == D_ID || col.ColumnName == Split) continue; double val1 = double.Parse(row[col.ColumnName].ToString()); double val2 = double.Parse(row2[col.ColumnName].ToString()); row[col.ColumnName] = (val1 - val2).ToString(); } } } }

Anupam Singh · Answer

:( quit confusing. can you share source code.

Vulpes · Answer

'F9' just formats the number to 9 decimal places - it doesn't actually round it off. To round it you can use: double val2 = double.Parse(row2[col.ColumnName].ToString()); val2 = Math.Round(val2 9);

Vulpes · Answer

Hi Anupam I know from Farhan's previous questions that he's creating these DataTables from CSV files rather than an RDBMS as I think you were assuming in your first post. He'll probably then be displaying the data in a Chart control. AFAICT a DataView would be no help here.

Anupam Singh · Answer

Hi Farhan you have to use join operator for these types of scenario : Here is the query using join select tbl1.D_IDtbl1.split(tbl1.value_2-tbl2.Value_1) as [Value_1] '' as [Value_2](tbl1.value_3-tbl2.Value_3) from Table1 tbl1 join Table2 tbl2 on tbl1.D_ID=tbl2.D_ID Hope it will help.

Farhan Shariff · Answer

I would have not thought F9 can be used to round off :) But How to round this val2 to 9 decimal places double val2 = double.Parse(row2[col.ColumnName].ToString());

Vulpes · Answer

It's this line that you'll need to change: row[col.ColumnName] = (val1 - val2).ToString(); since double values themselves don't know anything about formatting. To display the difference to 9 decimal places try: row[col.ColumnName] = (val1 - val2).ToString(F9);

Farhan Shariff · Answer

Here double val1 = double.Parse(row[col.ColumnName].ToString()); double val2 = double.Parse(row2[col.ColumnName].ToString()); Can I round off the val1 and val2 to 9 sice they are exponential Values .... I tried this but wont work NumberStyles styles; styles = NumberStyles.AllowExponent | NumberStyles.Number; double val1 = double.Parse(row[col.ColumnName].ToString()styles); double val2 = double.Parse(row2[col.ColumnName].ToString()styles);

Farhan Shariff · Answer

Yes it is working perfectly I had made mistake while passing table i passed wrong table as argument.Now I have to work on test cases

Farhan Shariff · Answer

Yes Vulpes you are perfectly rightthank you :) I am testing it still a long way to go.

Anupam Singh · Answer

Hi Vulps I think its good to use view instead of using code(C# VB or else.)  it will also improve the performance.

Farhan Shariff · Answer

hey Anup Thank you for the reply I have to loop(match) D_ID column in table1 and table 2 and then loop(match) column names and match (get value at that index) in both the table and then subtract. the column names and Id can be anything