An array that updates account values 1

[Tactical]

Hopefully someone can render some help here...

I'm importing a comma-delimited text file with each row containing a unique numeric identifier for each customer along with an account value among many other items. A customer may have more than just one account so I need a way to sum the value of all their accounts.

It my idea to use an array that'll be two columns and a variable number of rows. With each line read from the import file I would like to check if the unique identifier (customer) exists in the array, if not re-dim the array increasing the rows by one and store the unique identifier in column 1 and the value in column 2. If it does exists then I would like to add the account value to the existing value contained in the array.

I've only be scripting for awhile and have used arrays only for simpler tasks. Appreciate any help!

- Iris

 

[mrmovie]

if you have a unique identifier then i would suggest a dictionary object rather than array, the indexed searching .Exists will be much faster than looping through the array each time, plus the .Add means you dont have to redim everytime.
have the key as the unique ID, have the value whatever you want.,,,the rest of the comma delimited string?? or the Sum? of perhaps an object containing the properties for each account?? (i.e. create yourself a class)

 

[Tactical]

I'm not familar with dictionary objects - let alone coding them.

Even if an array isn't the most efficient method its the one method that I feel I have an understanding of - just need help coding it. If I get the array working then I can pursue learning/coding dictionary objects at a later time.

Still looking for help with coding an array...

- Iris

 

[mrmovie]

a dictionary object is an indexed array (hash table?)

Set objDict = CreateObject("Scripting.Dictionary")

For Each aLine In File
aArray = Split(aLine, ",")
strAccNo = aArray(0) 'lets say acc number is first field
If Not objDict.Exists(strAccNo) Then
objDict.Add strAccNo, CDbl(aArray(2)) 'invoice amount?
Else 'already there lets add it up
objDic.Item(strAccNo) = CDbl(objDic.Item(strAccNo)) + CDbl(aArray(2))
End If
Next

'look at the results
For Each aAccNo In objDict
Wscript.Echo aAccNo & "=" & objDict.Item(aAccNo)
Next

 

[mrmovie]

what part of coding an array are you having trouble with?

 

[Tactical]

That looks short and sweet...

I think I can work with that example, like I said, I don't have any experience with dictionary object but your example seems straight forward enough.

One more question, to then reference the value for other work how would I write a script along these lines..

If strAccNo = "Unique Identifier" Then
NewFieldValue = ????
End If

I'm not sure what parameter I would use after "If =" would it be along the lines of objDict.Exists(strAcctNo)?

And, if the If statement is true how would I reference the value (total).

Thanks for your help and patience!

- Iris

 

[PHV]

Have a look here:

http://msdn.microsoft.com/library/en-us/script56/html/b4a7ddb3-2474-49ef-8540-8d67a747c8db.asp

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

 

[Tactical]

Thanks PHV, I'm familiar with the site but mrmovie's example goes along way for me anyway in comparing the sample script with a resource like microsofts and others. Working with something rather than nothing just helps.

Anyways, mrmovie if you still care to comment I'd appreciate it. In the meantime, I'll be reading up on dictionary objects.

Thanks

- Iris

 

[Tactical]

Clicked post to fast...

So to retrieve the value am I correct in using the syntax NewFieldValue = objDict.Item{strAcctNo)

- Iris

 

[mrmovie]

yeap, to retrieve the 'Value'

you can do

objDic.Item(key)

you can also retrieve a Values collection and Keys collection.

before adding or retrieving data from a dic object i always do a .Exists first

you can also use a more traditional i iteration

For i = 0 To objDict.Count -1 ' Iterate the array.
s = s & objDict(i) & "<BR>" ' Create return string.
Next

....if you assign the value to an Object then you will want to do stuff like

For Each aThing In dicThings
Set objThing = dicThings.Item(aThing)
If objThing.Address = "xxx" Then
'
End If
Set objThing = Nothing
Next

 

[mrmovie]

as with any string comparison be careful of UCase and LCase for the dictionary 'key'. either insist that all keys are written in U or L or turn off the case sensetiveness of the .exists method...i will leave you to find that one out

 

[Tactical]

Thanks mrmovie.

 

[Tactical]

So can a dicObj contain more than one item for each key. Would it be as simple as adding the following line for the previous example:

objDict.Add strAccNo, CDbl(aArray(3))

I have read up on dictObj since my original post but this particular point still isn't clear to me???

- Iris

 

[mrmovie]

a dictionary's item can contain anything you want...

array, integer, string, another dictionary object, file object, folder object, user defined object (i.e. your own class) etc etc

but to answer 'can a dicObj contain more than one item for each key', no it cant. multiple key and item pairs is the order in the day. the beauty/power comes from assigning something 'special' to the item