Hi, I have some large excel file
from witch I have to select sum and count of distinct column “TEKUĆE STANJE DUGA”. I have to get this result
Could someone help me to do this?
Hi @Olivera_Kalinic,
What abot this expression
Input
Output
Code
dtInput.AsEnumerable.Sum(Function(x) CDbl(x("Number").ToString))
Number = YourColumnName
Thanks,
Rajkumar
I hope this is what you are looking at.
This will group the data with the column2 and get the count of each group and sum of each group
Dt - datatable in which data is present
Column2 - the column on which you want to group by
ColumntoSUM - column name of which you want the sum of
Use this in assign
Dt = (From d In Dt.AsEnumerable()
Group d By k=d("Column2").toString.Trim Into grp = Group
Let coun = grp.count
Let s = grp.Sum(function(x) Cdbl(x("columntoSUM").ToString))
Let ra = New Object(){k,s,coun}
Select r = Dt.Rows.Add(ra)).CopyToDataTable()
Hope this helps
Cheers
Can you, please, add also count of grouped column?
do one thing I guess your datatable is having more columns
so create a new datatable with 3 columns and datatype string using build datatable (newdt)
and then change this in the code
- coun = grp.Count.ToString
- s = grp.sum(function(x) x(“column”).ToString).ToString
- Select r = newdt.Rows.add(ra)).CopyToDatatable()
We are changing this because to write to same table the table already you have is having more columns than required(3).so we are creating a new datatable with 3 columns and adding data to that
cheers
This is my newDT
Table nakonFilteraDT contains just one column.
This is result after your suggestion
OK, I’ve tryed it at first, but error is at the beginning
What am I doing wrong?
As i mentioned already in row 6 in your expression editor you have to use newdt instead of nak…filter…dt
And i hope in build datatable properties you assigned output variable to newdt
Cheers
Now, I’m getting this error
Awesome you have blank values in your datatable ok
change this line
Let s = grp.sum(function(x) If(IsNumeric(x("column").ToString),CDBL(x("column").ToString),0)).ToString
make sure you replace the column with the appropriate column name
cheers
Here remove the first line and in second line replace column and also after zero there are 3 brackets make it 2
cheers
Let me give you the full expression after compiling my side. please have a look
(From d In nakonfilteradt.AsEnumerable()
Group d By k=d("TEKUĆE STANJE DUGA").toString.Trim Into grp = Group
Let coun = grp.count
Let s = grp.Sum(Function(x) If(IsNumeric(x("TEKUĆE STANJE DUGA").ToString.Trim),CDbl(x("TEKUĆE STANJE DUGA").ToString.Trim),0)).ToString
Let ra = New Object(){k,s,coun}
Select r = newdt.Rows.Add(ra)).CopyToDataTable()
Hope with this your issue will be resolved
Note: For copy paste make sure you replace the double quotes again in the code
cheers
prashant1603765
yedukondaluaregala
V_Roboto_V
sharazkm32
Aki1111
cristian_camilo_gonzalez
mively
ashokkarale
sonaliaggarwal47
postwick