I am reading from an excel file. Taking output in a datatable.
Now I want to use grouping LINQ query on this data table to get average of a column based on grouping done over other column.
Suppose column A contains account numbers and column “C” contains Units purchased. so my output will be average of units purchased grouped by account numbers. I want this output to be exported in another datatable.
How can I do that?
I am attaching my XAML in my reply below
Main.xaml (10.4 KB)
Hi @abskulkarni,
I have attached your example with slight changes. Please check it and confirm.
Main.xaml (11.0 KB)
You may also try it:
Assign iemResult to:
(From j As Object In DT.Rows _
Group By x = New With {Key .AccID = DirectCast(j, DataRow)("Account ID")} Into g = Group _
Select dtResult.LoadDataRow(New Object() {x.AccID, g.Average(Function(r) Convert.ToInt32(DirectCast(r, DataRow)("Units")))},False) _
)
Hi @acaciomelo
Many thanks to give a direction. However, it is giving error in loading Datarow into dtResult. The thing is, Linq query is able to give IEnumerable result of Object. But, that object needs to be cast into {string, Double} anonymous type to get loaded into dtResult. While doing that, it is giving below error in “Invoke Method” step. Below is the error.
Hi @acaciomelo
I have resolved it by writing some custom VB.NET code to parse IEnumerable(Object) result got from Linq query. I use this custom code in “Invoke Code” activity.
But thanks for giving me a direction. Also please let me know if there is an easy and out-of-box way to accomplish this.
Hi abskulkarni,
I’m running through the same problem. Could you please provide the xaml file which you have solved using vb.net invoke code.
Thank you in advance.
HI
Here is a sample linq and join
Dim save1 As datatable = new datatable
Dim save2 As datatable
save1.Columns.Add(“Periode”, GetType(String))
save1.Columns.Add(“SteFiscale”, GetType(String))
save1.Columns.Add(“Description Uc”, GetType(String))
save1.Columns.Add(“t1”, GetType(String))
save1.Columns.Add(“Compte”, GetType(String))
save1.Columns.Add(“Description Compte”, GetType(String))
save1.Columns.Add(“Site”, GetType(String))
save1.Columns.Add(“Description Site”, GetType(String))
save1.Columns.Add(“Solde Mois Precedent”, GetType(String))
save1.Columns.Add(“Soldef”, GetType(String))
save1.Columns.Add(“Etafi”, GetType(String))
console.writeline(“juste avant save1”)
save2 = (From a As datarow In dataTableDonneesSocieteFiscaleEnCours.AsEnumerable
Group Join b In datatableplancomptable.AsEnumerable
On a(“Compte”) Equals b(“Compte”)
Into Group
Let b = Group.FirstOrDefault
Select save1.LoadDataRow(New Object() {
a.Field(Of String)(“Periode”) ,
a.Field(Of String)(“SteFiscale”) ,
a.Field(Of String)(“Description Uc”) ,
a.Field(Of String)(“t1”) ,
a.Field(Of String)(“Compte”) ,
a.Field(Of String)(“Description Compte”) ,
a.Field(Of String)(“Site”) ,
a.Field(Of String)(“Description Site”) ,
a.Field(Of String)(“Solde Mois Precedent”) ,
a.Field(Of String)(“Soldef”) ,
If(b Is Nothing, “?”, b.Field(Of String)(“Etafi”) )
}, False
) ).copytodatatable()