Select a maximum value in an array.

[twoplustwo]

Hi guys,

Hope everyone is well

I have a table with data in the following format:

SettlementDate ForecastDate ForecastID Data....
01/01/2009 25/12/2008 1
01/01/2009 27/12/2008 2
01/01/2009 01/01/2009 3

I have an array of this data in a VBA routine that already does some other stuff. Which is the best way to select the max ForecastID (along with the data) from the array?

Thanks for any help.

 

[ajetrumpet]

you may have to loop through the array and find the max value of the dimension corresponding to the forecast ID field. if you do have to resort to that though, it would be better to add a field to your table to indicate what element number in the array corresponds to the maximum forecastID in that record in the table. the rest of the recs would be blank for this field.

that way you can pull that element number out of the array just by looking in the table's field instead of having to loop the arr() everytime to find the max ID in the appropriate dimension

 

[twoplustwo]

Thanks for the reply Adam.

Would you mind explaining the following a little: "add a field to your table to indicate what element number in the array corresponds to the maximum forecastID in that record in the table". It sounds like a PK of some sort but I already have the PK from the Oracle back end in the table.

 

[ajetrumpet]

Thanks for the reply Adam.

Would you mind explaining the following a little: "add a field to your table to indicate what element number in the array corresponds to the maximum forecastID in that record in the table". It sounds like a PK of some sort but I already have the PK from the Oracle back end in the table.

i am assuming you know how to use arrays, and what dimensions and elements are...(since you are already using one.)


(assumption - you are using an array with 5 dimensions in it, representing 5 fields in your table - 3rd dimension being the field that you want to search for)

to get the 3rd dimension in the element number that contains the information corresponding to the maximum foreCastID that is in the table, i would assume something like this would work:

dim targetID, targetVal, Result, i as integer

targetID = dmax("forecastID", "table")
tartgetVal = dlookup("field", "table", "[forecastID] = " & targetID)

For i = LBound(YOUR ARRAY, 3) To UBound(YOUR ARRAY, 3)
   if instr(YOUR ARRAY(i, i, i, i, i), targetVal) > 0 then
      debug.pring YOUR ARRAY(i, i, i, i, i)
         exit
   end if
next i

please backup your data before you try this. it's been awhile since i worked with arrays, and i did not test this.


the loop should print the entire record in the immed. window when it finds the record with the max forecastID...

 

[gemma-the-husky]

can't you do a dmax on the table that provided the data for the array?

otherwise it depends how big the array is.

if its small, then searching every record to find the biggest isnt much of an overhead,.

if its big, then it is - and in that case, you need a different data structure - either ensure the records are sorted as the array is populated, or alternatively, use a collection, or a temporary table.