Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 805
  • Last Modified:

Array manipulation help / VBScript

Hi everyone,

I have an array with 2275 items in it. Each is a string consisting of 5 items delimited by the "#" symbol, for example:

com1#0582#03/29/05 2:11:44 AM#1#0
com9#0522#03/31/05 2:29:25 AM#0#1
com 103#0596#03/02/05 12:45:54 AM#157270#59529
com6#0502#03/16/05 3:38:52 AM#2#1
com7#0500#03/14/05 2:48:22 AM#406#3207
comp2#0522#03/07/05 4:45:34 AM#81#290
com 103#0552#03/25/05 12:44:58 AM#2647#1050
com7#0500#03/14/05 12:49:52 PM#406#3207

the first part of each is a company number. This is not a fixed length, unfortunately.  The second is a subcompany, always 4 digits.  The third part of this is a date and time for a billing cycle, and is the focus of my loop. Out of the 2275 items in the loop, about 60 (this month, at least) need to be deleted.  For an example, look at the two 'com7' lines above.  Both have a cycle date of 03/14/05, but the first was earlier in the day.  In cases like this where the first two fields are the same, and the day is the same, I need to delete the earlier of the dates (the times will never be the same).  I'm using a loop to look through them all, and when the time is earlier I just clear out that item in the array.  Afterwards I go through and transfer the non-blank entries to another array.  The transfering of the non-blanks is very quick, takes about a second, but the loop to go through the actual entries takes about 32 seconds.  I'm hoping I can bring that down a bit with all of your help!

Here is the code I'm using to look through the items, in array named "u":

 for i = lbound(u) to ubound(u)
  if u(i)<>"" then
   a=split(u(i))
   for j = i + 1 to ubound(u)
    if u(j)<>"" then
     b=split(u(j))
     if a(0)=b(0) then
      if a(1)=b(1) then
       if day(a(2))=day(b(2)) then
        if a(2)>b(2) then u(j)="" else u(i)=""
       end if
      end if
     end if
    end if
   next
  end if
 next

This looks sloppy to me, but I can't figure out a better way to do this.  Can this be improved?

Thanks
Matt
0
mvidas
Asked:
mvidas
  • 6
  • 2
  • 2
2 Solutions
 
rettiseertCommented:
Try splitting all your values before starting the loop:

Dim SplittedValues(1 to 2275) as Variant
For I = 1 to 2275
   SplittedValues(I) = Split (u(i), "#")
Next

And now use SplittedValues( )( ) array instead of a and b arrays inside your loop. This way you dont have to split the same values again and again.

Also, save lbound(u) and ubound(u) into variables and use them:

Instead of :

for i = lbound(u) to ubound(u)

use:

lower = lbound(u)
upper = ubound(u)
for i= lower to upper
0
 
mvidasAuthor Commented:
Hi rettiseert (tree sitter?),
I set the ubound and lbound to variables, though when watching in the task manager it didnt make a difference at all.  I'll still keep it that way though, it is better programming.
As for creating the array of arrays, that is a great idea!  I always forget about the ability to put arrays within arrays.  Took 18 seconds off the runtime, down to 16s.  Here is the code as it stands now:

 ilb=lbound(u)
 iub=ubound(u)
 redim splitu(iub)
 for i = ilb to iub
  splitu(i)=split(u(i),"#")
 next
 for i = ilb to iub
  if u(i)<>"" then
   for j = i + 1 to iub
    if u(j)<>"" then
     if splitu(i)(0)=splitu(j)(0) then
      if splitu(i)(1)=splitu(j)(1) then
       if day(splitu(i)(2))=day(splitu(j)(2)) then
        if splitu(i)(2)>splitu(j)(2) then u(j)="" else u(i)=""
       end if
      end if
     end if
    end if
   next
  end if
 next

Can anything further be done with this?
I have another portion of my code that takes much longer, but I'm still trying to figure out where the worst time killer is, and once that is done I'll post a new Q.
0
 
PSSUserCommented:
Would a quicker way be to load the items into a collection?
If you combine fields 1 and 2 and the date part of fiel 3 into the key for the items in the collection, you could use this to try a call back an item from the collection. If it exists, you update the details with the newer details (check the new record's time is later than the item's current time property).
If the item doesn't exist you add a new item to the collection.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
mvidasAuthor Commented:
Can you use collections in vbscript? If so, how to you set it up? Since you can't dimension items, I don't know how I would set it up. I tried just using

 col.Add "hi"
 col.Add "everyone"
 For Each itm In col
  MsgBox itm
 Next

But I got an error with col.  Googling for "vbscript collection" only brings up the various collections that vbscript uses and not how to use an actual collection object. I wanted to use a collection but couldnt figure out the syntax
0
 
mvidasAuthor Commented:
I'm playing with using a dictionary object right now, I know I can use that in vbs and im hoping I can get it to work (using the serial value of the date/time as the key), see how that turns out
0
 
mvidasAuthor Commented:
And it worked well!

Here is my final code:

 ilb=lbound(u)
 iub=ubound(u)
 redim splitu(iub)
 for i = ilb to iub
  splitu(i)=split(u(i),"#")
 next
 set dic=createobject("scripting.dictionary")
 for i = ilb to iub
  thekey=splitu(i)(0)&splitu(i)(1)&clng(datevalue(splitu(i)(2)))
  if dic.exists(thekey) then
   loc=dic.item(thekey)
   if splitu(loc)(2)<splitu(i)(2) then dic.item(thekey)=i
  else
   dic.add thekey, i
  end if
 next

I used only the integer/date portion of the date-time field as the key (concatenated to the end of the first two fields), miniscule amount of time.

I'm going to award 400 points to rettiseert (for the array-in-an-array idea), and 100 to PSSUser (for suggesting collection, which eventually triggered me to use a dictionary object instead).  Thanks for the help! If/when I post a new question later, I'll paste a link here in case either of you are around.

Thanks
Matt
0
 
rettiseertCommented:
Thanks! (and yes!, it's tree sitter...!)

And now with the dictionary and array, how much time you saved in this loop?
0
 
mvidasAuthor Commented:
The entire subroutine now takes about 2-3 seconds; less than a second for this dictionary part now (down from 32!), about 1-2 seconds for a consolidating portion (which brings the 2212 items that remaining after removing the bad cycles down to about 376 lines by consolidating the same records where company and subcompany are the same), and then less than a second to sort those.
I'm working on bringing down the part that takes about 1 minute and 45 seconds, but that is what parses a 300,000 line text file down to the 2275 items.  I'm sure I'll be posting a question later!
Thanks again
0
 
PSSUserCommented:
Hi,

sorry I didn't get a chance to post back yesterday, it was the end of my working day here and I had a few things I need to get done before I left.

For future reference, you can use collections in VBScript. Here is a useful tutorial on the subject:
http://www.4guysfromrolla.com/webtech/092399-1.shtml
0
 
mvidasAuthor Commented:
Thanks PSS, it wasn't a problem that you didn't get to post back yesterday. I've long understood that everyone here is in a different part of the world, part of what makes EE so great.

I appreciate the link! I still consider myself new to vbscript (written less than 1000 lines of code im sure), and only moderately above amateur in vb (though I know the excel object and excel vba like the back of my hand), and that link is great(!) information on how to use classes in vbscript. I'm using vbscript directly from a .vbs file in windows (not asp or anything), and I really didn't think I'd get to use classes here.  Not sure if I'm going to integrate a class into the script I was asking about above, but it is still great information!  I'm going to keep that link for future reference.

Again, thanks much!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now