One to many matching.

Results 1 to 2 of 2

Thread: One to many matching.

  1. #1
    Join Date
    Dec 1969

    Default One to many matching.

    I&#039;ve posted on this before, and now have some code developed, that, logically workes with whole numbers, but throw some decimals in there, and it does not work properly...<BR><BR>Here is what I have:<BR>&#060;%<BR>Set Conn = Server.CreateObject("ADODB.Connection")<BR> Conn.Open "Provider=SQLOLEDB;Data Source=(local);User ID=user;Password=pass;Initial Catalog=mycatalog"<BR> Set rs = SERVER.CREATEOBJECT("ADODB.RECORDSET")<BR><BR><BR> dim keys<BR>keys = ""<BR><BR>function getcombos2 (goal, start)<BR><BR> dim total<BR> total = 0.0<BR> dim i<BR> <BR> &#039;Loop through the array from large to small<BR> for i = start to 1 step -1<BR> <BR> if total &#062;= goal then<BR> &#039;Over-shot the goal<BR> exit for<BR> end if<BR> <BR> <BR> if debits(value,i) &#062; goal then<BR> &#039;The current number is greater than the goal<BR> total = total + getcombos2(goal-total-1,i) <BR> elseif debits(value,i) + total &#062; goal then<BR> &#039;Current number would put goal over the top<BR> total = total + getcombos2(goal-total-1,i-1)<BR> else <BR> if total &#060; goal then<BR> &#039;If you find a value that will fit in the total, <BR> &#039;and still be less than the goal, set the total, and update the keys.<BR> total = total + formatnumber(debits(value,i),2)<BR> keys = keys & debits(id,i)& " :: " & debits(value,i) & ","<BR> <BR> end if<BR> end if<BR> <BR> next<BR> &#039;Set the value of the function<BR> getcombos2 = formatnumber(total,2)<BR>end function<BR><BR><BR><BR><BR><BR><BR>sql = "select uniqueaccountid, creditamt from reconcile where status &#060;&#062; &#039;R&#039; and status &#060;&#062; &#039;E&#039; and creditamt &#062; 0"<BR> sql, conn<BR>&#039;Get the array of credits<BR>credits = rs.GetRows<BR>rs.close<BR>sql = "select uniqueaccountid, debtamt from reconcile where status &#060;&#062; &#039;R&#039; and status &#060;&#062; &#039;E&#039; and debtamt &#062; 0 order by debtamt asc"<BR>rs.Open sql, conn<BR>&#039;Get the array of debits<BR>debits = rs.GetRows<BR>rs.close<BR><BR>&#039;Set key values for multi dimentional array... easier to read<BR>id = 0<BR>value = 1<BR><BR>for baseloop = 1 to ubound(credits,2)<BR> &#039;Loop through the credits<BR> &#039;Let the user know which value they are solving for <BR> Response.Write("Finding:" & credits(value,baseloop) &"<BR>")<BR> &#039;Set the current count (what record you are working on<BR> currentcount = ubound(credits,2) + 1<BR> <BR> do<BR> &#039;Reset the keys<BR> keys = ""<BR> &#039;Decrease the current count by one<BR> currentcount = currentcount - 1<BR> &#039;Set the new value, using the getcombos2 function<BR> newvalue = getcombos2(credits(value,baseloop),currentcount) <BR> &#039;Let the user know what value came back from the function<BR> Response.Write("VALUE:" & newvalue &"<BR>")<BR> &#039;Let the user know the key values from the function<BR> Response.Write(keys & "<BR><BR><BR>")<BR> &#039;Finish off the loop<BR> loop until newvalue = credits(value,baseloop) or currentcount = 1<BR> <BR>next<BR>%&#062;<BR><BR>EEK! I know, lots of code... I am getting a script timeout error, looks like it is stuck in an infinate loop, or it is just taking a long time to process, any idea on how to re-structure it to speed up the process any?<BR><BR>Synopsis of what this does:<BR>First, the program grabs two dimentional array of two recordsets (Keys and values). The program will loop through the values in the credit array, and search for multiple matches in the debit array for a combination of values that make up the one value in the credit array. <BR><BR>Any idea on how to speed up the processing on this bad boy, or maybe a better way of doing it?<BR><BR>Thanks,<BR><BR>-- Whol

  2. #2
    Join Date
    Dec 1969

    Default RE: One to many matching.

    I don&#039;t see where you&#039;re setting newvalue at.<BR><BR>Maybe there&#039;s your problem...<BR><BR>If it is a float though, NEVER check equality on a float.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts