Counting multiple occurances in an array 2

[JohnCR]

Hello,

I'm trying to find the best way to count elements of an array. I have any number of occurances of dates, such as mm/dd/yy and I would like to count the seperate occurances so that I can plot them on a chart.

The data I'm currently working with is;

7/6/04
7/6/04
7/6/04
7/5/04
7/5/04
7/5/04
7/10/04
7/11/04
7/15/04
7/15/04
7/22/04
6/3/04
6/3/04

Can anyone provide the best possible way to code this. I'm thinking that a recursion type loop is probably the best. So far all I have is this:

arr = date.items()
j = 0
t = 0
count = 0
For i = 0 to date.count-1
If arr(j) = arr(t) Then
count = count + 1
j = j + 1
End If
carr(i) = count
t = t + 1
Next

Which scrolls through the dates, incrementing the counter. I'm also able to change the code and get the first count, which is 3, but after that I'm lost.

TIA,

JohnCR

 

[EBGreen]

This should work:

arr = Array("7/6/04", "7/6/04", "7/6/04", "7/5/04", "7/5/04", "7/5/04", _
            "7/10/04", "7/11/04", "7/15/04", "7/15/04", "7/22/04", _
            "6/3/04", "6/3/04")
            
Set oDic = CreateObject("Scripting.Dictionary")
For i = 0 To UBound(arr)
	If oDic.Exists(arr(i)) Then
		oDic.Item(arr(i)) = oDic.Item(arr(i)) +1
	Else
		oDic.Add arr(i), 1
	End If
Next

For Each strDate In oDic.Keys()
	WScript.Echo "The date " & strDate & " occures " & oDic.Item(strDate) & " time(s)"
Next

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[PHV]

You may consider a Dictionary object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JohnCR]

Well your example does work, TomThumb, however I need to get the date from a recordset. BTW, I'm impressed. I've been working on this for a few days. Thanks!

This is what I have at the moment

Dim sSQL
Dim connection
Dim rs
Dim cn
Dim i

Set objFSO = CreateObject("Scripting.FileSystemObject")

sSQL = "SELECT * FROM TestLog"

Set cn = CreateObject("ADODB.connection")
Set rs = CreateObject("ADODB.recordset")
sConnString="DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=" & "C:\data\data base" & "\log.mdb" & ";"
cn.open = sConnString
rs.Open sSQL, cn

arr = rs.GetRows(,,"Date Found")

'arr = Array("7/6/04", "7/6/04", "7/6/04", "7/5/04", "7/5/04", "7/5/04", _
'"7/10/04", "7/11/04", "7/15/04", "7/15/04", "7/22/04", _
'"6/3/04", "6/3/04")

Set oDic = CreateObject("Scripting.Dictionary")
For i = 0 To UBound(arr)
If oDic.Exists(arr(i)) Then
oDic.item(arr(i)) = oDic.item(arr(i)) +1
Else
oDic.Add arr(i), 1
End If
Next

For Each strDate In oDic.Keys()
WScript.Echo "The date " & strDate & " occures " & oDic.item(strDate) & " time(s)"
Next

Set oDic = Nothing
Set rs = Nothing
Set cn = Nothing

I would expect this to work but I get a 'Subscript out of range: 'arr' message.

JohnCR

 

[Tarwn]

Thats because the GetRows method returns a 2-dimensional array. The first index is field numbr, the second index is row number, so in this case you want to loop through the second index. You could make the following modifications:

For i = 0 To UBound(arr,2)
    If oDic.Exists(arr(0,i)) Then
        oDic.item(arr(0,i)) = oDic.item(arr(0,i)) +1
    Else
        oDic.Add arr(0,i), 1
    End If
Next

however, since your pulling this from the database, why not let the atabase do the work? You could modify your SQL statement to pull back the field value and count all by itself:

sSQL = "SELECT DISTINCT DateFound, Count(DateFound) as NumOccurences FROM TestLog GROUP BY DateFound"

'... later on

Dim results
If Not rs.EOF The rs.MoveFirst
Do Until rs.EOF
   results = results & "The Date " & rs("DateFound") & " occurs " & rs("NumOccurences") & " times." & vbCrLf
   rs.MoveNext
Loop

Wscript.echo results

Either way, hope this helps,

-T

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
The never-completed website:

http://www.tiernok.com/

 

[JohnCR]

Well I considered the database 'Select' option but I'm using other parts of the database in other areas of the script.

The modification should work just fine. I've tested this little bit of code and will add it to the complete script later today.

Is it possible to have multiple 'Select' statements in the same script? For instance, can I "SELECT * FROM ...." in one part of the script and later send another "SELECT" statement to the same record set?

Again, thanks for your help

 

[EBGreen]

Are you sure that arr is being populated at all? What happens if you do this:
arr = rs.GetRows(,,"Date Found")
WScript.Echo Join(arr, ", ")


[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]