Help with loop

I have a DB with a list of unix epoch times and count values. Each time an action runs, a record is written to the DB along with a count value, and this happens multiple times per day.

I have a function already that converts the unix epoch time to a short date, all I need for this exercise.

What I want is to load the table in, loop through every row and add them to an array. But where there are duplicate days, I sum the count values together - i.e. if 23/06/24 appears 5 times, the single value I retain is the sum of all of those 5 count values.

I think that the best way to do this is via a dictionary object, but i can’t get my head around the logic i need. i think it should be:

  1. add date and value pair to dictionary object.
  2. if there is a collision on the date (as a key), then find the key/value pair and add the count value to the existing value
  3. continue loop

Can anyone help out with the dictionary object part? i have the loop through the table fine, i just need to deduce how to capture the collision and sum the value instead

You asked a very similar question about a week ago:

The answers presented in that thread are still correct. Either use the dictionary code someone offered, or use SQL to sum the data before your code sees it (probably faster).

2 Likes

right, but in that thread there is epoch time. this time i only want to use a short date hence the new question

OK you used epoch time after being helped by others here BUT you cannot take the time to figure this out yourself?
This might borderline taking advantage of the kindly help others offer here on their own free time

not at all. i have been trying to solve this for hours. i can’t get my head around detecting the collision in the dictionary.

and why the attack? if you don’t like it, just don’t reply. no need to be hostile for a genuine question i have been struggling with.

Dim count As Integer = MyDictionary.Lookup("key", 0)
MyDictionary.Value("key") = count + 1

Use the Dictionary.Lookup method to check for the key’s existence. The second parameter to Lookup is the value to return if the key doesn’t exist. So, if the key already exists then we read the number stored in the dictionary; if it doesn’t exist then we get 0 back. Then add 1 and store it in the dictionary, overwriting the key if it already existed.

Dim list() As String ' assume populated
Dim dict As New Dictionary
For i As Integer = 0 To Ubound(list)
  Dim key As String = list(i)
  Dim count As Integer = dict.Lookup(key, 0)
  dict.Value(key) = count + 1
Next

No attack, as another poster pointed out you have asked a similar question that was answered

thank you for this, it was a help.

it uncovered another error I had in that I was declaring the dictionary wrong, i had forgotten the NEW keyword.

Thanks Andrew and Eric.