Delete records from VBS array

[Andrew91]

Hi community!

Trying to figure out with the following task:

I have source 2-dimensional array in VBS script, which i need to read in the loop and save only those records, which have the max TimeStamp per each Name:
Source:



Name Type TimeStamp
Andrew Type1 45678
Andrew Type2 45679
Andrew Type1 46678
Andrew Type2 45660
Mike Type3 6543
Mike Type4 653
Tom Type1 643


Expected array:


Name Type TimeStamp
Andrew Type1 46678
Mike Type3 6543
Tom Type1 643


I tried to use a classic way of fetching the max element using following code, but for not a bit confused how to apply it for 2-dim array:

Dim arr,max

arr=array(6,5,8,2,0,9)

max=arr(0)

for i=0 to ubound(arr)

if max< arr(i) then
max=arr(i)

end if

next


Thanks for your help!

 

[mikrom]

Hi Andrew91,

Here is an example which reads your input array, and creates an output array which only contains for every name one record with maximum timestamp:

[COLOR=#0000ff]' input array[/color]
my_inp_array [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]Array[/color][COLOR=#804040][b]([/b][/color][COLOR=#008080]Array[/color][COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]"Andrew"[/color][COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]"Type1"[/color][COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]45678[/color][COLOR=#804040][b]),[/b][/color]_
                     [COLOR=#008080]Array[/color][COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]"Andrew"[/color][COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]"Type2"[/color][COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]45679[/color][COLOR=#804040][b]),[/b][/color]_
                     [COLOR=#008080]Array[/color][COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]"Andrew"[/color][COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]"Type1"[/color][COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]46678[/color][COLOR=#804040][b]),[/b][/color]_
                     [COLOR=#008080]Array[/color][COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]"Andrew"[/color][COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]"Type2"[/color][COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]45660[/color][COLOR=#804040][b]),[/b][/color]_
                     [COLOR=#008080]Array[/color][COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]"Mike"[/color][COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]"Type3"[/color][COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]6543[/color][COLOR=#804040][b]),[/b][/color]_
                     [COLOR=#008080]Array[/color][COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]"Mike"[/color][COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]"Type4"[/color][COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]653[/color][COLOR=#804040][b]),[/b][/color]_
                     [COLOR=#008080]Array[/color][COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]"Tom"[/color][COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]"Type1"[/color][COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]643[/color][COLOR=#804040][b]))[/b][/color]

wscript[COLOR=#804040][b].[/b][/color]echo[COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]"Input Array:"[/color][COLOR=#804040][b])[/b][/color]
[COLOR=#804040][b]call[/b][/color] show_array[COLOR=#804040][b]([/b][/color]my_inp_array[COLOR=#804040][b])[/b][/color]
                 
wscript[COLOR=#804040][b].[/b][/color]echo[COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]"Processing ..."[/color][COLOR=#804040][b])[/b][/color]

[COLOR=#0000ff]' create Dictionary object[/color]
[COLOR=#804040][b]set[/b][/color] my_hash [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]CreateObject[/color][COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]"Scripting.Dictionary"[/color][COLOR=#804040][b])[/b][/color]

[COLOR=#804040][b]for[/b][/color] j[COLOR=#804040][b]=[/b][/color][COLOR=#ff00ff]0[/color] [COLOR=#804040][b]to[/b][/color] [COLOR=#008080]Ubound[/color][COLOR=#804040][b]([/b][/color]my_inp_array[COLOR=#804040][b])[/b][/color]
 [COLOR=#0000ff] 'wscript.echo((j+1) & ": " & my_inp_array(j)(0) & "- " & my_inp_array(j)(2))[/color]
 [COLOR=#0000ff] ' get key and timestamp value[/color]
  key [COLOR=#804040][b]=[/b][/color] my_inp_array[COLOR=#804040][b]([/b][/color]j[COLOR=#804040][b])([/b][/color][COLOR=#ff00ff]0[/color][COLOR=#804040][b])[/b][/color]
  timestamp [COLOR=#804040][b]=[/b][/color] my_inp_array[COLOR=#804040][b]([/b][/color]j[COLOR=#804040][b])([/b][/color][COLOR=#ff00ff]2[/color][COLOR=#804040][b])[/b][/color]

 [COLOR=#0000ff] ' add records to hash (i.e. dictionary)[/color]
  [COLOR=#804040][b]if[/b][/color] my_hash[COLOR=#804040][b].[/b][/color][COLOR=#a020f0]exists[/color][COLOR=#804040][b]([/b][/color]key[COLOR=#804040][b])[/b][/color] [COLOR=#804040][b]then[/b][/color]
    [COLOR=#804040][b]if[/b][/color] timestamp [COLOR=#804040][b]>[/b][/color] my_hash[COLOR=#804040][b].[/b][/color][COLOR=#a020f0]item[/color][COLOR=#804040][b]([/b][/color]key[COLOR=#804040][b])([/b][/color][COLOR=#ff00ff]2[/color][COLOR=#804040][b])[/b][/color] [COLOR=#804040][b]then[/b][/color]
     [COLOR=#0000ff] ' replace existing record with record which has greater timestamp[/color]
      my_hash[COLOR=#804040][b].[/b][/color][COLOR=#a020f0]item[/color][COLOR=#804040][b]([/b][/color]key[COLOR=#804040][b])[/b][/color] [COLOR=#804040][b]=[/b][/color] my_inp_array[COLOR=#804040][b]([/b][/color]j[COLOR=#804040][b])[/b][/color] 
    [COLOR=#804040][b]end[/b][/color] [COLOR=#804040][b]if[/b][/color]
  [COLOR=#804040][b]else[/b][/color]
    my_hash[COLOR=#804040][b].[/b][/color][COLOR=#a020f0]item[/color][COLOR=#804040][b]([/b][/color]key[COLOR=#804040][b])[/b][/color] [COLOR=#804040][b]=[/b][/color] my_inp_array[COLOR=#804040][b]([/b][/color]j[COLOR=#804040][b])[/b][/color]
  [COLOR=#804040][b]end[/b][/color] [COLOR=#804040][b]if[/b][/color]
[COLOR=#804040][b]next[/b][/color]

[COLOR=#0000ff]' create output array dynamically  [/color]
[COLOR=#804040][b]dim[/b][/color] my_out_array[COLOR=#804040][b]()[/b][/color]
[COLOR=#0000ff]' set the dimension to number of keys in the hash[/color]
n [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]Ubound[/color][COLOR=#804040][b]([/b][/color]my_hash[COLOR=#804040][b].[/b][/color][COLOR=#a020f0]keys[/color][COLOR=#804040][b])[/b][/color]
[COLOR=#804040][b]redim[/b][/color] my_out_array[COLOR=#804040][b]([/b][/color]n[COLOR=#804040][b])[/b][/color]

[COLOR=#0000ff]' convert hash records into array records [/color]
j [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]0[/color]
[COLOR=#804040][b]for[/b][/color] [COLOR=#804040][b]each[/b][/color] key [COLOR=#804040][b]in[/b][/color] my_hash
 [COLOR=#0000ff] ' wscript.echo(key & " ==> " & my_hash.item(key)(2))[/color]
  my_out_array[COLOR=#804040][b]([/b][/color]j[COLOR=#804040][b])[/b][/color] [COLOR=#804040][b]=[/b][/color] my_hash[COLOR=#804040][b].[/b][/color][COLOR=#a020f0]item[/color][COLOR=#804040][b]([/b][/color]key[COLOR=#804040][b])[/b][/color]
  j [COLOR=#804040][b]=[/b][/color] j [COLOR=#804040][b]+[/b][/color] [COLOR=#ff00ff]1[/color]
[COLOR=#804040][b]next[/b][/color]

wscript[COLOR=#804040][b].[/b][/color]echo[COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]"... Done."[/color][COLOR=#804040][b])[/b][/color]

[COLOR=#0000ff]' show results[/color]
wscript[COLOR=#804040][b].[/b][/color]echo[COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]"Output Array:"[/color][COLOR=#804040][b])[/b][/color]
[COLOR=#804040][b]call[/b][/color] show_array[COLOR=#804040][b]([/b][/color]my_out_array[COLOR=#804040][b])[/b][/color]

[COLOR=#0000ff]' -----------------------------------------------------------[/color]
[COLOR=#804040][b]sub[/b][/color] show_array[COLOR=#804040][b]([/b][/color]a[COLOR=#804040][b])[/b][/color]
  [COLOR=#804040][b]for[/b][/color] k[COLOR=#804040][b]=[/b][/color][COLOR=#ff00ff]0[/color] [COLOR=#804040][b]to[/b][/color] [COLOR=#008080]Ubound[/color][COLOR=#804040][b]([/b][/color]a[COLOR=#804040][b])[/b][/color]
    wscript[COLOR=#804040][b].[/b][/color]echo[COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]"Name: "[/color] [COLOR=#804040][b]&[/b][/color] a[COLOR=#804040][b]([/b][/color]k[COLOR=#804040][b])([/b][/color][COLOR=#ff00ff]0[/color][COLOR=#804040][b])[/b][/color] [COLOR=#804040][b]&[/b][/color] [COLOR=#ff00ff]vbTab[/color] [COLOR=#804040][b]& _[/b][/color]
                 [COLOR=#ff00ff]"Type: "[/color] [COLOR=#804040][b]&[/b][/color] a[COLOR=#804040][b]([/b][/color]k[COLOR=#804040][b])([/b][/color][COLOR=#ff00ff]1[/color][COLOR=#804040][b])[/b][/color] [COLOR=#804040][b]&[/b][/color] [COLOR=#ff00ff]vbTab[/color] [COLOR=#804040][b]& _[/b][/color]
                 [COLOR=#ff00ff]"TimeStamp: "[/color] [COLOR=#804040][b]&[/b][/color] a[COLOR=#804040][b]([/b][/color]k[COLOR=#804040][b])([/b][/color][COLOR=#ff00ff]2[/color][COLOR=#804040][b]))[/b][/color]
  [COLOR=#804040][b]next[/b][/color]
[COLOR=#804040][b]end[/b][/color] [COLOR=#804040][b]sub[/b][/color]

The output is:

u:\Work\VBscript>cscript /NoLogo andrew91.vbs
Input Array:
Name: Andrew    Type: Type1     TimeStamp: 45678
Name: Andrew    Type: Type2     TimeStamp: 45679
Name: Andrew    Type: Type1     TimeStamp: 46678
Name: Andrew    Type: Type2     TimeStamp: 45660
Name: Mike      Type: Type3     TimeStamp: 6543
Name: Mike      Type: Type4     TimeStamp: 653
Name: Tom       Type: Type1     TimeStamp: 643
Processing ...
... Done.
Output Array:
Name: Andrew    Type: Type1     TimeStamp: 46678
Name: Mike      Type: Type3     TimeStamp: 6543
Name: Tom       Type: Type1     TimeStamp: 643

 

[strongm]

You can replace all of

[blue][green]' set the dimension to number of keys in the hash[/green]
n = UBound(my_hash.Keys)
ReDim my_out_array(n)

[green]' convert hash records into array records[/green]
j = 0
For Each Key In my_hash
  wscript.echo(key & " ==> " & my_hash.item(key)(2))
  my_out_array(j) = my_hash.Item(Key)
  j = j + 1
Next[/blue]

with

[blue]my_out_array = my_hash.Items[/blue]

 

[mikrom]

Thanks strongm