Using LINQ to build table sum by staff no

Help Studio
, ,
1

Hi all,

I am trying on using LINQ to build a data table to illustrate the sum of payment amount of each staff (sum by staff no).

I am not familiar with coding so I just simply copy the LINQ code from other sources to build my own.

I first assign the IEnumerable as code below:

(From p In dt_pymt_table_ori.AsEnumerable() Group p By obj_p= New With {Key.P_Staff_No=p.Item(“Staff_No”)} Into MG= Group Select new with { .Staff_No=obj_p.P_Staff_No,.Paid_Amt=MG.Sum(Function( r ) Double.Parse(r.Item(“Paid_Amt”).ToString()))}).ToList

Then I assign a string of the table with code below:

String.Join(“,”,IEnum(0).ToString.Split(“,“c).Select(Function(item) item.ToString.Split(”=“c)(0).Replace(”{”,“”).Trim))+System.Environment.NewLine+ String.Join(System.Environment.NewLine,IEnum.Select(Function(row) String.Join(“,”,row.ToString.Split(“,“c).Select(Function(item) item.ToString.Split(”=“c)(1).Replace(”}”,“”).Trim.ToString))))

After that I generate the data table with the abovementioned string.
When I paste the table to excel I found the amount do summed up by staff but the staff no is missing.

image

May I know what I have missed leading to this.

For your further information, the staff number is a field with only number or alphabet with numbers.

Thanks a lot !

2

Hi @Wilfred_Hung ,

@ppr can help you here easily.

3

ensure you have an empty target datatable in place - dtTarget
this can be e.g. created with the build datatable activity and defining 2 cols: Staff_No, Sum

use an assign activity:
LHS: dtTarget
RHS:

(From d In dt_pymt_table_ori.AsEnumerable()
Group d By k= d(“Staff_No”).toString.Trim Into grp=Group
Let s = grp.Sum(Function( x ) Double.Parse(x(“Paid_Amt”).ToString))
Let ra = new Object(){k,s}
Select r=dtTarget.Rows.Add(ra)).CopyToDataTable

Other outputs e.g. a dictionary would be possible as well

Also have a look here:

1 Like
4

Thanks for both !
I will look into it and try.