Solved

Merge two arrays

Posted on 2001-09-13
18
500 Views
Last Modified: 2012-06-27
I have 2 arrays, both with the same # of rows, but different # of fields.  I would like to take these tow arrays and merge them into a larger array.  (or just add one array to another)  Any Ideas?
0
Comment
Question by:scotiaceilidh
  • 7
  • 4
  • 3
  • +3
18 Comments
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
You can create a dynamic array and enlarge it using ReDim.

Is that the type of array you mean?  Or are you talking about a recordset?
0
 
LVL 4

Expert Comment

by:dilligaffuq
Comment Utility
Same # of rows but, diff # of records.

Do you mean you want to like...append to the right.  Not append to the bottom.

dill
0
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
ReDim will only work with the new rows being IDENTICAL to the existing rows.

Since the two arrays appear to in fact be different, what do you think you need when you say you want to "merge" them?  How do you want to handle the items that are in one array, and NOT in the Other?
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
Actually, you can redim the second dimension of a 2-dimensional array without concern.

So this works:

Dim SomeArray() as string
Redim SomeArray(10,30)
Redim Preserve SomeArray(10,50)

But this loses data:

Dim SomeArray() as string
Redim SomeArray(10,30)
Redim Preserve SomeArray(20,30)

...or so the MS documentation indicates.

Personally, I've successfully done the first one without loss of existing data.  However, when doing that, I had to change the understanding of the array dimensions.

0
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
but the SECOND dimension is the NUMBER of ROWS (at least in most implementations)...what is gained if you still have the first dimension as 10?

you now have 10 instances of 50 items, rather than 10 instance of 30...what have you accomplished with that?
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
Well, first of all it depends on the requirements.  If the requirements are to add columns (but not rows) it works just fine.

If the requirements do not indicate how the data is to be stored, then you can simply transpose your array and call the first element columns and the second one rows.  This may require more work, so it has to be a seriously evaluated consideration.

Personally, given the apparent requirements, I'd probably go with either a collection or a recordset.

And actually, I think we're all confused about what the actual requirements are, so we can only guess at what solution would work best.
0
 
LVL 6

Expert Comment

by:holli
Comment Utility
#!/usr/bin/perl
@a=(1,2,4); @b=(3,4,5); @all=(@a,@b);

couldn't resist.
holli
0
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
Holli,
   Cute but irrelevant. read the original question:

"I have 2 arrays, both with the same # of rows, but different # of fields.  I would like to take these
tow arrays and merge them into a larger array.  (or just add one array to another)  Any Ideas? "

Your post appears to have the same number of FIELDS (3).  The original quest explicitly states SAME # or ROWS, diffrent FILEDS in those rows.  

The original question appears to be adding APPLES and ORANGES - does NOT make any sense, at least not to me.
0
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
the only thing that would come close to making any sense would be to APPEND the fields from ONE array ONTO the FIELDS from the other array, but only then IF (big IF) the rows were relevant to the same "THING" (essentially a One-To-One JOIN to two tables).  There was no indication that the data in a given row in one array was related to the data in the same row (or ANY row, for that matter) in the second array.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 4

Expert Comment

by:dilligaffuq
Comment Utility
holli - That was too funny.

Art - I have to agree with you.  We have no idea how the arrays look.

Is it like:
Array1:
ssn
firstname
lastname

Array2:
ssn
street
city
state
zip

And we have a 1 to 1 relationship with ssn and we want 1 array with ALL the fields.

Or is it like:
Array1:
ssn
firstname
lastname
phonenumber
street
city
state
zip

Array2
ssn
firstname
lastname
city
state
zip

And we have no relationship and want to append the 2nd array to the bottom of the 1st array and just leave the non-existant fields null?

Wouldn't it be pretty cool if scotiaceilidh would respond to clear up the questions.

dill
0
 
LVL 1

Author Comment

by:scotiaceilidh
Comment Utility
The first Array is based on a record set, created from
'Place Record set into Array for Display
        aResults1 = rs.GetRows(, , 1).

The above array has only one field.

The next field is created from UDT

Public Type CustomArray
 Value1 As String
 Value2 As String
 Value3 As String
 Value4 As String
End Type

and populated by

aResults2(i).Value1 = sJob
        aResults2(i).Value2 = Status(lngRetVal)
        aResults2(i).Value3 = Format(mstrLastRunDate, "dddd, mmm d yyyy")
        aResults2(i).Value4 = mstrLastRunStatus

I am currently trying to place aResults1 into Value1 of aResults2.  I am getting a subscript error.  Here is all the code.

'Place Record set into Array for Display
        aResults1 = rs.GetRows(, , 1)
       
       
'Close Database
Call objConn.CloseRecordset

Set rsJob = objConn.GetRecordset(sSQL)
       
'=======================================================================================
'HERE IS THE BUILDING OF THE SECOND ARRAY WHICH IS THE 2ND HALF OF DISPLAYED DATA
'=======================================================================================
'Loop thru the Job Names
iRows = UBound(aResults1, 2)
iCols = UBound(aResults1, 1)
'Stop at the end
iStop = iRows

'Set increment for Array
i = 0
For iRowLoop = iStart To iStop - 1
    For iColLoop = 0 To iCols
            sJob = aResults1(iColLoop, iRowLoop)
            'Now that we have the Job Name, we call a function to return the "Job Status"
            '"Date Last Run", and the "Last Run Outcome".
            Dim lngRetVal As Integer
           
       
'create the SQL DMO wrapper object
Set objGRC_SQLDMO = CreateObject("GRC_SQLDMO.clsGRC_SQLDMO")
               
            varJobName = rsJob!strJobName
           'lngRetVal holds the status returned
           lngRetVal = objGRC_SQLDMO.SQLIsJobRunning(strServer:=varServer, _
                                    strUser:="StartJob", _
                                    strPassword:="dnjobber", _
                                    strJobName:=varJobName, _
                                    strLastRunDate:=mstrLastRunDate, _
                                    strLastRunStatus:=mstrLastRunStatus)
   
        'Place the values into the 2nd array
        aResults2(i).Value1 = sJob
        aResults2(i).Value2 = Status(lngRetVal)
        aResults2(i).Value3 = Format(mstrLastRunDate, "dddd, mmm d yyyy")
        aResults2(i).Value4 = mstrLastRunStatus

'Increment counter to add next row to the array.

i = i + 1
         
                 
    Next
   
Next

'========================================================================================

'start building the table
sHTML = "<Table Width = 550 align=left border=0 CELLSPACING=3 CELLPADDING=1 bgcolor =Silver>"

'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'Loop thru the Job Names
iRows = UBound(aResults2, 2)
iCols = UBound(aResults2, 1)
'Stop at the end
iStop = iRows
   

'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


    For iRowLoop = iStart To iStop
        sHTML = sHTML & "<TR>"
       
        For iColLoop = 0 To iCols
            sHTML = sHTML & "<TD width=100>"
            sHTML = sHTML & aResults1(iColLoop, iRowLoop) & " "
            sHTML = sHTML & "</TD>"
            '===================================================
            'Merge the 2nd Array with the first.
            '===================================================
            'sHTML = sHTML & "<TD width=100>"
            'sHTML = sHTML & aResults2(iColLoop, iRowLoop) & " "
            'sHTML = sHTML & "</TD>"
            '===================================================
        Next
        sHTML = sHTML & "</TR>"
    Next

sHTML = sHTML & "</table>"

'Write to Web Page
OrderByTable = sHTML


0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
scotiaceilidh, is this not working that you want to find a way to merge the arrays?

I'm thinking that if you really want to do as you say, you can try something like this:

Dim BigArray() as string
...

ReDim BigArray(iCols, iRows)

Then start transferring the data from the two arrays into BigArray and use as necessary.
0
 
LVL 1

Author Comment

by:scotiaceilidh
Comment Utility
rspahitz,
Yes, Thank you.  But I do not know how to transfer the data from the two arrays into a larger one.  I will start with this and the Redim, but suspect I will be stuck on inserting the data into the Big array.
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
Gee, it looks like you already have the code...

...

iRows1 = UBound(aResults1, 2)
iCols1 = UBound(aResults1, 1)
iRows2 = UBound(aResults1, 2)
iCols2 = UBound(aResults1, 1)

ReDim BigArray(iCols1 + iCols2, iRows1 + iRows2)

For iRowLoop = 0 to iRows1
  For iColLoop = 0 To iCols1
     BigArray(iColLoop, iRowLoop) = aResults1(iColLoop, iRowLoop)
  Next iColLoop
Next iRowLoop

For iRowLoop = 0 to iRows2
  For iColLoop = 0 To iCols2
     BigArray(iColLoop + iCols1 + 1, iRowLoop +  iRows1 + 1) = aResults1(iColLoop, iRowLoop)
  Next iColLoop
Next iRowLoop

...
I haven't tested this, no it may not be perfect, but should be close enough that you can figure it out.
0
 
LVL 1

Author Comment

by:scotiaceilidh
Comment Utility
BigArray(iColLoop + iCols1 + 1, iRowLoop +  iRows1 + 1) = aResults1(iColLoop, iRowLoop)


Here is where I get a sub script error.  I think this is due to this array being a UDT and not one created from a recordset.  My teleprompting indicates that there is just one column in the 2nd array, although I mean there to be several.  Any Ideas.  
0
 
LVL 9

Expert Comment

by:miron
Comment Utility
You might get it all clearer codewise by creating a class module and in that class module put both data objects, that constitute array dimentions.
then declare an array of this class type objects;
next, create one dimentional array of class objects and assign value to class members via dot operator.
0
 
LVL 22

Accepted Solution

by:
rspahitz earned 40 total points
Comment Utility
scotiaceilidh, I think I forgot to add one in the redim:

ReDim BigArray(iCols1 + iCols2 + 1, iRows1 + iRows2 + 1)

If the same problem recurs, tell us the values of the variables iColLoop, iCols1, iRowLoop, iRows1 when it gives you the error.
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
Glad to be of help, scotiaceilidh.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now