Asp Forum - Merging two 2dimensional array based on a common key

avi

9/2/2011 10:03:00 PM

Hello,

I have a MainArray of n X m and a SecondaryArray of r X s . Both are 2
dimensional

I want to merge both arrays into a third one of n rows and (m+s-1)
columns based on a key located both in a column of MainArray and a
column of SecondaryArray . The resulting array is formed by MainArray
and right to it by all the other columns of SecondaryArray that match
according to the columns keys

To be clearer, I want to reproduce exactly the Vlookup Excel function

Many thanks
Avi

5 Answers

GS

9/3/2011 12:38:00 AM

avi explained on 9/2/2011 :
> Hello,
>
> I have a MainArray of n X m and a SecondaryArray of r X s . Both are 2
> dimensional
>
> I want to merge both arrays into a third one of n rows and (m+s-1)
> columns based on a key located both in a column of MainArray and a
> column of SecondaryArray . The resulting array is formed by MainArray
> and right to it by all the other columns of SecondaryArray that match
> according to the columns keys
>
> To be clearer, I want to reproduce exactly the Vlookup Excel function
>
> Many thanks
> Avi

Try 'dumping' both arrays into an Excel spreadsheet, do your sorting,
then 'dump' the resulting range (now n rows by 2 columns) back into a
variant. This will result in a 2D array of n rows by 2 columns (m,s).

--
Garry

Free usenet access at http://www.eternal-sep...
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Jimekus

9/3/2011 3:36:00 AM

On Sep 3, 10:03 am, avi <avi...@bezeqint.net.il> wrote:
> Hello,
>
> I have a MainArray of n X m and a SecondaryArray of r X s . Both are 2
> dimensional
>
> I want to merge both arrays into a third one of n rows and (m+s-1)
> columns based on a key located both in a column of MainArray and a
> column of SecondaryArray . The resulting array is formed by MainArray
> and right to it by all the other columns of SecondaryArray that match
> according to the columns keys
>
> To be clearer, I want to reproduce exactly the Vlookup Excel function
>
> Many thanks
> Avi

The way I handle such problems is to ensure that my original data
comes tabbed delimited from a RichTextBox. This way I can move it in
and out of a FlexGrid.Table using the .Clip function. Once the data is
in a table it is easy to change the number of columns, and then pre-
calculate data to fill numeric arrays.

One inherent MSFlexGrid problem that I can't seem to fix, though, is
when I have to export the clip back to the textbox for saving. This
works fine for most sizes but when the table reaches into the
megabytes, any reference to this .clip property freezes my program.
I'm specifically referring to a table extracted from the Internet
Movie Database covering 2000 titles.

I would really like to do what you are doing in order to merge my
collection with the Dramatica Story-Engine database. Assigning data to
the .Clip property is very fast even for extremely large tables. Just
be sure that after a certain size you stay away from reading .clip as
I think it is built by concatenation each time.

A cure is sorely needed.

GS

9/3/2011 3:01:00 PM

GS explained on 9/2/2011 :
> avi explained on 9/2/2011 :
>> Hello,
>>
>> I have a MainArray of n X m and a SecondaryArray of r X s . Both are 2
>> dimensional
>>
>> I want to merge both arrays into a third one of n rows and (m+s-1)
>> columns based on a key located both in a column of MainArray and a
>> column of SecondaryArray . The resulting array is formed by MainArray
>> and right to it by all the other columns of SecondaryArray that match
>> according to the columns keys
>>
>> To be clearer, I want to reproduce exactly the Vlookup Excel function
>>
>> Many thanks
>> Avi
>
> Try 'dumping' both arrays into an Excel spreadsheet, do your sorting, then
> 'dump' the resulting range (now n rows by 2 columns) back into a variant.
> This will result in a 2D array of n rows by 2 columns (m,s).

Just to be clear, my suggestion is a 'concept'. The actual number of
columns in the resulting array would be whatever columns are retained
from both. So then, if MainArray was 10 x 4 and SecondaryArray was 10 x
4 then ResultArray would be 10 x 8-1 as the match column of
SecondaryArray does not duplicate. (If I understand you correctly!)

--
Garry

Free usenet access at http://www.eternal-sep...
ClassicVB Users Regroup! comp.lang.basic.visual.misc

GS

9/3/2011 3:41:00 PM

Jimekus presented the following explanation :
> On Sep 3, 10:03 am, avi <avi...@bezeqint.net.il> wrote:
>> Hello,
>>
>> I have a MainArray of n X m and a SecondaryArray of r X s . Both are 2
>> dimensional
>>
>> I want to merge both arrays into a third one of n rows and (m+s-1)
>> columns based on a key located both in a column of MainArray and a
>> column of SecondaryArray . The resulting array is formed by MainArray
>> and right to it by all the other columns of SecondaryArray that match
>> according to the columns keys
>>
>> To be clearer, I want to reproduce exactly the Vlookup Excel function
>>
>> Many thanks
>> Avi
>
> The way I handle such problems is to ensure that my original data
> comes tabbed delimited from a RichTextBox. This way I can move it in
> and out of a FlexGrid.Table using the .Clip function. Once the data is
> in a table it is easy to change the number of columns, and then pre-
> calculate data to fill numeric arrays.
>
> One inherent MSFlexGrid problem that I can't seem to fix, though, is
> when I have to export the clip back to the textbox for saving. This
> works fine for most sizes but when the table reaches into the
> megabytes, any reference to this .clip property freezes my program.
> I'm specifically referring to a table extracted from the Internet
> Movie Database covering 2000 titles.
>
> I would really like to do what you are doing in order to merge my
> collection with the Dramatica Story-Engine database. Assigning data to
> the .Clip property is very fast even for extremely large tables. Just
> be sure that after a certain size you stay away from reading .clip as
> I think it is built by concatenation each time.
>
> A cure is sorely needed.

Interesting approach. Thanks for sharing that with us!

I suspect, though, that however 'fast' it may seem to be.., I've always
found that read/write to an object is much slower than read/write to
memory.

<FWIW>
I believe what Avi is trying to do is to merge 2 sources of data as
would be found, say, on 2 separate Excel worksheets and combine the
results into a 3rd worksheet. Normally, I would 'dump' the data from
wks1/wks2 into variant vars (va1/va2) and loop them outer against inner
for collecting my values (based on matches found) to be added to a new
array ('if' found) OR stored and added later.

By 'stored and added later' I mean the collected data is added to a
delimited string var where each column is delimited by <|> (pipe
character) and each record is delimited by <vbCrLf> so I can readily
'dump' the contents to a text file, new array, spreadsheet, or
whatever. In Avi's case, the process is 2-step: 1:collect/assemble
data; 2:put data into new array.

The 1st step is handled by the looping I previously mentioned.

The 2nd step is handled using Split(sData, vbCrLf) to 'dump' the
assembled data into vaTemp so we know the UBound for the final array.
Then ReDim FinalArray(MaxRows, MaxCols) and load it as we would any
other multi-dim array using Split(vaTemp(i), "|") for each column
element.

Alternatively, we can cut the process short by leaving vaTemp as the
FinalArray and process that only as required, OR build FinalArray as an
array of arrays.

Option2:
Load/merge the data into recordsets using standard SQL on the source
tables. This WILL be slower but might be an easy way out!<-g>

--
Garry

Free usenet access at http://www.eternal-sep...
ClassicVB Users Regroup! comp.lang.basic.visual.misc

avi

9/3/2011 5:55:00 PM

On 3 sep, 18:41, GS <g...@somewhere.net> wrote:
> Jimekus presented the following explanation :
>
>
>
>
>
>
>
>
>
> > On Sep 3, 10:03 am, avi <avi...@bezeqint.net.il> wrote:
> >> Hello,
>
> >> I have a MainArray of n X m and a SecondaryArray of r X s . Both are 2
> >> dimensional
>
> >> I want to merge both arrays into a third one of n rows and (m+s-1)
> >> columns based on a key located both in a column of MainArray and a
> >> column of SecondaryArray . The resulting array is formed by MainArray
> >> and right to it by all the other columns of SecondaryArray that match
> >> according to the columns keys
>
> >> To be clearer, I want to reproduce exactly the Vlookup Excel function
>
> >> Many thanks
> >> Avi
>
> > The way I handle such problems is to ensure that my original data
> > comes tabbed delimited from a RichTextBox. This way I can move it in
> > and out of a FlexGrid.Table using the .Clip function. Once the data is
> > in a table it is easy to change the number of columns, and then pre-
> > calculate data to fill numeric arrays.
>
> > One inherent MSFlexGrid problem that I can't seem to fix, though, is
> > when I have to export the clip back to the textbox for saving. This
> > works fine for most sizes but when the table reaches into the
> > megabytes, any reference to this .clip property freezes my program.
> > I'm specifically referring to a table extracted from the Internet
> > Movie Database covering 2000 titles.
>
> > I would really like to do what you are doing in order to merge my
> > collection with the Dramatica Story-Engine database. Assigning data to
> > the .Clip property is very fast even for extremely large tables. Just
> > be sure that after a certain size you stay away from reading .clip as
> > I think it is built by concatenation each time.
>
> > A cure is sorely needed.
>
> Interesting approach. Thanks for sharing that with us!
>
> I suspect, though, that however 'fast' it may seem to be.., I've always
> found that read/write to an object is much slower than read/write to
> memory.
>
> <FWIW>
> I believe what Avi is trying to do is to merge 2 sources of data as
> would be found, say, on 2 separate Excel worksheets and combine the
> results into a 3rd worksheet. Normally, I would 'dump' the data from
> wks1/wks2 into variant vars (va1/va2) and loop them outer against inner
> for collecting my values (based on matches found) to be added to a new
> array ('if' found) OR stored and added later.
>
> By 'stored and added later' I mean the collected data is added to a
> delimited string var where each column is delimited by <|> (pipe
> character) and each record is delimited by <vbCrLf> so I can readily
> 'dump' the contents to a text file, new array, spreadsheet, or
> whatever. In Avi's case, the process is 2-step: 1:collect/assemble
> data; 2:put data into new array.
>
> The 1st step is handled by the looping I previously mentioned.
>
> The 2nd step is handled using Split(sData, vbCrLf) to 'dump' the
> assembled data into vaTemp so we know the UBound for the final array.
> Then ReDim FinalArray(MaxRows, MaxCols) and load it as we would any
> other multi-dim array using Split(vaTemp(i), "|") for each column
> element.
>
> Alternatively, we can cut the process short by leaving vaTemp as the
> FinalArray and process that only as required, OR build FinalArray as an
> array of arrays.
>
> Option2:
> Load/merge the data into recordsets using standard SQL on the source
> tables. This WILL be slower but might be an easy way out!<-g>
>
> --
> Garry
>
> Free usenet access athttp://www.eternal-sep...
> ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thanks to all

I started to implement array Garry's approach and it seems to be the
optimal one although the SQL one seems also promising

Avi

microsoft.public.vb.general.discussion

Merging two 2dimensional array based on a common key

avi

GS

Jimekus

GS

GS

avi

x Login to ForumsZone