c# - Trouble adding/merging multiple DataTables together with different row counts -
i have process creates dataset each year since 2012. each dataset has 4 datatables. datatable 1 2012 same structure datatable 1 2013, , same datatable 1 2014.
i need following (this not valid - representation):
dstotals.tables[0] = ds2012.tables[0] + ds2013.tables[0] + ds2014.tables[0]; dstotals.tables[1] = ds2012.tables[1] + ds2013.tables[1] + ds2014.tables[1]; dstotals.tables[2] = ds2012.tables[2] + ds2013.tables[2] + ds2014.tables[2]; etc. i achieving so:
year = convert.toint32(datetime.now.year); while (2012 <= year) { var main = new dataset(); var dst = dataaccess.getnumbers(byreferral, year.tostring()); //gets table 1 year var dst1 = dataaccess.getnumbers(bysource, year.tostring()); //gets table 2 year var dst2 = dataaccess.getnumbers(bymedium, year.tostring()); //gets table 3 year var dst3 = dataaccess.getnumbers(bycampaign, year.tostring()); //gets table 4 year var tablenumber = 0; foreach (datatable table in dst.tables) { var copy = table.copy(); copy.tablename = "table" + tablenumber.tostring(); main.tables.add(copy); tablenumber++; } foreach (datatable table in dst1.tables) { var copy = table.copy(); copy.tablename = "table" + tablenumber.tostring(); main.tables.add(copy); tablenumber++; } foreach (datatable table in dst2.tables) { var copy = table.copy(); copy.tablename = "table" + tablenumber.tostring(); main.tables.add(copy); tablenumber++; } foreach (datatable table in dst3.tables) { var copy = table.copy(); copy.tablename = "table" + tablenumber.tostring(); main.tables.add(copy); tablenumber++; } process.ds = main; //global dataset variable var grandtotaldata = addupdata(dst, dst1, dst2, dst3, process.ds, year); year--; } private static dataset addupdata(dataset dst, dataset ds1, dataset ds2, dataset ds3, dataset ds, int year) { if (ds.tables[0].rows.count < dst.tables[0].rows.count) { (var = 0; < ds.tables[0].rows.count; i++) { processful(i, dst, tableint: 0, tablenumber: 0); } } else if (ds.tables[0].rows.count > dst.tables[0].rows.count) { (var = 0; < dst.tables[0].rows.count; i++) { processful(i, dst, tableint: 0, tablenumber: 0); } } else { (var = 0; < dst.tables[0].rows.count; i++) { processful(i, dst, tableint: 0, tablenumber: 0); } } if (ds.tables[1].rows.count < dst.tables[1].rows.count) { (var = 2; < ds.tables[1].rows.count; i++) { processful(i, dst, tableint: 1, tablenumber: 1); } } else if (ds.tables[1].rows.count > dst.tables[1].rows.count) { (var = 2; < dst.tables[1].rows.count; i++) { processful(i, dst, tableint: 1, tablenumber: 1); } } else { (var = 2; < dst.tables[1].rows.count; i++) { processful(i, dst, tableint: 1, tablenumber: 1); } } if (ds.tables[2].rows.count < dst.tables[2].rows.count) { (var = 2; < ds.tables[2].rows.count; i++) { processful(i, dst, tableint: 2, tablenumber: 2); } } else if (ds.tables[2].rows.count > dst.tables[2].rows.count) { (var = 2; < dst.tables[2].rows.count; i++) { processful(i, dst, tableint: 2, tablenumber: 2); } } else { (var = 2; < dst.tables[2].rows.count; i++) { processful(i, dst, tableint: 2, tablenumber: 2); } } if (ds.tables[3].rows.count < ds1.tables[0].rows.count) { (var = 0; < ds.tables[3].rows.count; i++) { processful(i, ds1, tableint: 3, tablenumber: 0); } } else if (ds.tables[3].rows.count > ds1.tables[0].rows.count) { (var = 0; < ds1.tables[0].rows.count; i++) { processful(i, ds1, tableint: 3, tablenumber: 0); } } else { (var = 0; < ds1.tables[0].rows.count; i++) { processful(i, ds1, tableint: 3, tablenumber: 0); } } and on, method 12 tables in same way.
private static void processful(int i, dataset dst, int tableint = 50, int tablenumber = 50) { process.ds.tables[tableint].rows[i][1] = (convert.todecimal(process.ds.tables[tableint].rows[i][1].tostring()) + convert.todecimal(dst.tables[tablenumber].rows[i][1].tostring())).tostring(); process.ds.tables[tableint].rows[i][2] = (convert.todecimal(process.ds.tables[tableint].rows[i][2].tostring()) + convert.todecimal(dst.tables[tablenumber].rows[i][2].tostring())).tostring(); process.ds.tables[tableint].rows[i][3] = (convert.todecimal(process.ds.tables[tableint].rows[i][3].tostring()) + convert.todecimal(dst.tables[tablenumber].rows[i][3].tostring())).tostring(); process.ds.tables[tableint].rows[i][4] = (convert.todecimal(process.ds.tables[tableint].rows[i][4].tostring()) + convert.todecimal(dst.tables[tablenumber].rows[i][4].tostring())).tostring(); process.ds.tables[tableint].rows[i][5] = (convert.todecimal(process.ds.tables[tableint].rows[i][5].tostring()) + convert.todecimal(dst.tables[tablenumber].rows[i][5].tostring())).tostring(); process.ds.tables[tableint].rows[i][6] = (convert.todecimal(process.ds.tables[tableint].rows[i][6].tostring()) + convert.todecimal(dst.tables[tablenumber].rows[i][6].tostring())).tostring(); process.ds.tables[tableint].rows[i][7] = (convert.todecimal(process.ds.tables[tableint].rows[i][7].tostring()) + convert.todecimal(dst.tables[tablenumber].rows[i][7].tostring())).tostring(); process.ds.tables[tableint].rows[i][8] = (convert.todecimal(process.ds.tables[tableint].rows[i][8].tostring()) + convert.todecimal(dst.tables[tablenumber].rows[i][8].tostring())).tostring(); process.ds.tables[tableint].rows[i][9] = (convert.todecimal(process.ds.tables[tableint].rows[i][9].tostring()) + convert.todecimal(dst.tables[tablenumber].rows[i][9].tostring())).tostring(); process.ds.tables[tableint].rows[i][10] = (convert.todecimal(process.ds.tables[tableint].rows[i][10].tostring()) + convert.todecimal(dst.tables[tablenumber].rows[i][10].tostring())).tostring(); process.ds.tables[tableint].rows[i][11] = (convert.todecimal(process.ds.tables[tableint].rows[i][11].tostring()) + convert.todecimal(dst.tables[tablenumber].rows[i][11].tostring())).tostring(); process.ds.tables[tableint].rows[i][12] = (convert.todecimal(process.ds.tables[tableint].rows[i][12].tostring()) + convert.todecimal(dst.tables[tablenumber].rows[i][12].tostring())).tostring(); process.ds.tables[tableint].rows[i][13] = (convert.todecimal(process.ds.tables[tableint].rows[i][13].tostring()) + convert.todecimal(dst.tables[tablenumber].rows[i][13].tostring())).tostring(); process.ds.tables[tableint].rows[i][14] = (convert.todecimal(process.ds.tables[tableint].rows[i][14].tostring()) + convert.todecimal(dst.tables[tablenumber].rows[i][14].tostring())).tostring(); } the processful method adds data global dataset variable.
now approach work, if tables have same number of columns , same rows. luckily, tables have same number of columns. column count constant. however, year year, there divergent number of rows. table 0, lists possible referral places ie: google, facebook, blog etc. similar thing tables 1,2,3. each year, or month month, row might added. table 0 2014 has 3 other referral types, or perhaps 3 new ones , 1 no longer there ie: twitter, pinterest, bing in rows blog no longer referral year.
i need more robust way of adding datatables if 1 datatable has 30 rows , has 15, add ones match , leave others alone. way @ end, global dataset variable have totals of years , contain 4 tables in all. have referrals 2012, 2013, 2014 etc in table 0, adding numbers if referral still exists.
all of total data being written spreadsheet (this part taken care of). once totaled up, update rows accordingly in spreadsheet , add if necessary.
here snapshot of table 0 2012 (dummy data, actual schema):
source jan feb mar apr may jun jul aug sep oct nov dec average median total 8.0 8.0 4.0 4.0 22.03.0 2.0 0.0 0.0 0.0 0.0 0.0 4.3 2.5 51.0 blog 2.0 1.0 0.0 1.0 0.0 0.0 1.0 0.0 0.0 0.0 0.0 0.0 0.4 0.0 5.0 facebook 13.05.0 12.015.064.08.0 14.00.0 0.0 0.0 0.0 0.0 10.9 6.5 131.0 forum 0.0 0.0 0.0 1.0 15.02.0 2.0 0.0 0.0 0.0 0.0 0.0 1.7 0.0 20.0 google 0.0 0.0 0.0 0.0 1.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.1 0.0 1.0 continues , ends (median, average, total under source column):
median 2 1 1 1 6 1 1 0 0 0 0 0 1 0.0 0.0 average 13. 5.8 6.8 5.7 23.2 5.8 5.3 0.0 0.0 0.0 0.0 0.0 5.5 0.0 0.0 total 245 40 122 103 417 105 96 0 0 0 0 0 0 0 0 also of note, datatables tables have been pivoted in sql (hence above)
Comments
Post a Comment