Problem with group by query

Help Studio
1

Hi. Could anyone help me with modifying query below

(From d In newDT.AsEnumerable() Group d By k=d(“Column2”).toString.Trim Into grp = Group Let coun = grp.Sum(Function(x) If((x(“Column3”).ToString.Trim.IsNumeric),CDbl(x(“Column3”).ToString.Trim),0)).ToString Let s = grp.Sum(Function(x) If((x(“Column4”).ToString.Trim.IsNumeric),CDbl(x(“Column4”).ToString.Trim),0)).ToString Let ra = New Object(){k,s,coun} Select r = newdt.Rows.Add(ra)).CopyToDataTable()

This is grouping table

image
image1157×424 18.7 KB

by Column2 (“NKS”), the result of grouping by is

image
image693×378 43.9 KB

Although it works for these 3 column, I have to add rest of table columns (Column0, Column1 , Column5) to the result of query from the top of the post.

How can I manage it?

2

@Olivera_Kalinic

You can add any column like below

(From d In newDT.AsEnumerable() Group d By k=d(“Column2”).toString.Trim Into grp = Group Let coun = grp.Sum(Function(x) If((x(“Column3”).ToString.Trim.IsNumeric),CDbl(x(“Column3”).ToString.Trim),0)).ToString Let s = grp.Sum(Function(x) If((x(“Column4”).ToString.Trim.IsNumeric),CDbl(x(“Column4”).ToString.Trim),0)).ToString Let c1 = grp.Select(function(x) x("Column5").ToString).First() Let ra = New Object(){k,s,coun,c1} Select r = newdt.Rows.Add(ra)).CopyToDataTable()

Let c1 = grp.Select(function(x) x("Column5").ToString).First() This is the line which is added…you can add more for more columns…and add the corresponding value in this line Let ra = New Object(){k,s,coun,c1}

Hope this helps

Cheers

3

@Anil_G

This is my new query

(From d In newDT.AsEnumerable() Group d By k2=d(“Column2”).toString.Trim Into grp = Group
Let coun = grp.Sum(Function(x) If((x(“Column3”).ToString.Trim.IsNumeric),CDbl(x(“Column3”).ToString.Trim),0)).ToString
Let s = grp.Sum(Function(x) If((x(“Column4”).ToString.Trim.IsNumeric),CDbl(x(“Column4”).ToString.Trim),0)).ToString
Let k0 = grp.Select(Function(x) x(“Column0”).ToString)
Let k1 = grp.Select(Function(x) x(“Column1”).ToString)
Let k5 = grp.Select(Function(x) x(“Column5”).ToString)
Let ra = New Object(){k0,k1,k2,s,coun,k5} Select r = newdt.Rows.Add(ra)).CopyToDataTable()

with three new columns (k0, k1, k5),

but I’m getting result below

image
image1769×467 105 KB

4

@Olivera_Kalinic

I see that you missed .First() after the select statement please include the same or you can as well use (0)

Cheers

5

@Anil_G

I’ve attached results:

Sheet1 : input table that has to be grouped by Column2
Sheet2: Data after grouping, but just with 3 columns. That result I have to get, but with added columns Column0, Column1 and Column5
Sheet3: Result of last query (after adding .First() like you sugested) - so, result is not correct after adding 3 columns
testwritenewfilePRIMJER.xlsx (12.5 KB)

6

@Olivera_Kalinic

May I know what is not correct…From the other column are you looking for any specific data?

What I gave you is to pick the first row data always from each group of data that is obtained… Are you looking for something else?

if yes please provide the details of what you want to see in other columns and accordingly I can give you the formula

cheers