Get the Count of column values (possible values) in a Data Table using LINQ and extension methods?

Help Something Else
1

I have a table like this.

STATUS LEVEL
RETURN IN
ORDER OUT
RETURN OUT
RETURN IN
SHIP OUT
ORDER OUT
RETURN OUT
SHIP IN
ORDER OUT
ORDER OUT


Need the output like this

 

STATUS COUNT
RETURN 4
SHIP 2
ORDER 4
VIEW 0
BOOK 0
TOTAL 10


The possible values of status col are

 

POSSIBLE STATUS VALUE
RETURN
SHIP
ORDER
VIEW
BOOK

Suggest LINQ query for this. 

2

@kumar.varun2

lets assume following:

  • the data with the values are stored in on datatable (build datatable, read range) - dtKeys
  • the data (Status, Level, RETURN, IN…) are stored in a datatable - dtData

do following:
build datatable - configure a dt with 2 cols: - dtReport

  • Status - String
  • Count - Int32

Use an assign activity:
left side: dtReport
right side:

(From d in dtKeys.AsEnumerable
let cnt = dtData.AsEnumerable.Where(Function (x) x("STATUS").toString.Trim.Equals(d(0).toString.Trim)).Count
let ra = new Object(){d(0), cnt}
Select dtReport.Rows.Add(ra)).CopyToDataTable
2 Likes
3

It should be x(“STATUS”) instead of x(“LEVEL”). Am I right?

4

Hi @kumar.varun2

I think u can try with groupby linq method too

5

How?

6

@kumar.varun2
Yes,please try with Status

Groupby would not Count the Keys which are not present in dtData e.g Book. Thats why the Statement was formulated on where base

7

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.