Is it possible to select values from different datatables and sum them?

Hi
I’m currently working with two headerless data tables and i need to join them and sum two elements from them. in sql would something like this:

Select (a.Num + b.Num) as Result from xTable a join zTable b on a.id =b.id;

is it possible to do something similar using the items of the datatables?

Hey.
(I’m not an expert) But one simple way would be to use the Merge DataTable activity, then get Distinct rows on column “id” then sum the “Num” column using linq in vb.net

For example,
Merge Data Table activity: b into a
Assign activity: a.DefaultView.ToTable(true, "id", "Num").Sum(Function(r) If(IsNumeric(r("Num")),r("Num"),0) )
or
Assign activity: a.DefaultView.ToTable(true, a.Columns(0).ColumnName, a.Columns(1).ColumnName ).Sum(Function(r) If(IsNumeric(r("Num")),r("Num"),0) )
or
Assign activity: a.DefaultView.ToTable(true, a.Columns.Cast(Of DataColumn).Select(Function(c) c.ColumnName).ToArray ).Sum(Function(r) If(IsNumeric(r("Num")),r("Num"),0) ).Sum(Function(r) If(IsNumeric(r("Num")),r("Num"),0) )

I provided 3 alternate ways to use the ColumnNames in the ToTable() to get distinct rows. You can also use the index instead of “Num” columnname in the Sum part.

There could also be other ways to accomplish too, but hope this helps.

Regards.

Thanks for your answer, that would work if the tables had column names, but they don’t have them, so to filter them i have to do this:

assign activity dt = rcom.AsEnumerable().Where(Function(drRow) drRow.Item(4).ToString="NT").CopyToDataTable and

assign activity dt2 = tare.AsEnumerable().Where(Function(drRow) drRow.Item(4).ToString = "RT").CopyToDataTable.

and after that i have to join both of those tables and then perform something like

select Integer.Parse(dt.item(4).toString +dt2.item(4).toString) where dt.item(3).toString = dt2.item(3).toString

Maybe I misunderstood. Do you need to sum the Number column of the rows that match the id, and merge that way?

Also, look over the solution discussed here: Linq Group By and Sum
using GroupBy and Sum to see if that’s what you are looking for.