DATATABLE with 4,00,000 records and need count based on conditions

Help
,
1

Hi Team,

I am have a DATATABLE (lets say dtA) with 4,00,000 records. (one of the columns names is “score”.

Now I want to find the count of certain conditions.
Eg:- dtA.rows.count (where score is more than 5 and less then 15)

Also the.l count of dtA.rows.count (where Score is more than 16 and less then 35)

Also the count of dtA.rows.count (where score is more than 50 and less then 70)

Also the count of dtA.rows.count (where score is more then 80 and less then 100)

Now I know that it can be achieved using followed methods:-

  1. filter data table(using this 4 times for 4 lakh records)

Input- dtA
Output-dtA
“score” > “5”
Or score < 15, etc etc etc

OR

  1. for each row (using this 4 times for 4lakh records or using 4 if conditions in a single loop)

  2. LiNQ (which I don’t know how. Inputs are welcome)

4)please add if there is some other option also.

I need an option which will take less execution time to get the counts of each condition since there are 4lakh (and ofcourse more than that records)…

Can also provide linQ to achieve this.

Thanks in advance,
Regards
@hacky

2

Try the code below.

Dim outputDt As DataTable = inputDt.AsEnumerable().Where(Function(r) r.Field(Of Integer)("Score") > 5).CopyToDataTable()

If you are particular about speed, think of Python too.

3

Hi @hacky
you can use this linq query to filter dataTable accordingly,

  1. dtA1 = (dtA.AsEnumerable.Where(Function(s) Convert.ToDouble(s(“score”)) > 5 And Convert.ToDouble(s(“score”)) < 16).ToArray).CopyToDataTable
  2. dtA2 = (dtA.AsEnumerable.Where(Function(s) Convert.ToDouble(s(“score”)) > 16 And Convert.ToDouble(s(“score”)) < 35).ToArray).CopyToDataTable
  3. dtA3 = (dtA.AsEnumerable.Where(Function(s) Convert.ToDouble(s(“score”)) > 50 And Convert.ToDouble(s(“score”)) < 70).ToArray).CopyToDataTable
  4. dtA4 = (dtA.AsEnumerable.Where(Function(s) Convert.ToDouble(s(“score”)) > 80And Convert.ToDouble(s(“score”)) < 100).ToArray).CopyToDataTable

Use Assign here,
count1 = dtA1.Rows.Count
count2 = dtA2.Rows.Count
count3 = dtA3.Rows.Count
count4 = dtA4.Rows.Count

Thats it. :slight_smile:

1 Like
6

@samir,

i TRIED YOUR APPROACH, WHILE WRITING IN THE dtA, i AM GETTING AS:

INPUT STRING WAS NOT IN THE CORRECT FORMAT.

7

@hacky

take a look at this xaml, you’ll get the idea.
Sequence1.xaml (7.7 KB)

builddata msg1
msg2

8

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