Match entries in one array to another array and create a new array.

[Fro2]

I have been trying for quite sometime to do what should be a simply operation.  I already have a portion of my code working exectpt for this part which is similar to the post below.

http://forums.ni.com/t5/LabVIEW/pulling-out-values-from-a-1D-array-based-on-look-up-table/m-p/404524

However I am trying to match the first two columns in Array 1 to those in Array 2 and create Array 3.

 

Please can anyone help?  I in a time crunch.  Thanks in advance.

[tbob]

Let me get this right.  You want to compare Array 1, column 0 and column 1 with Array 2, columns 0 and 1.  If the two columns match, then you want to include that row in Array 3, with the columns of Array 1 followed by the columns in Array 2?  If either column does not match, do not include in Array 3.  Is this right?

If so, feed Array 1 and Array 2 into a For Loop with indexing enabled.  Array 1 and 2 must have the same number of rows, or the loop will only process the shortest array.  Inside the loop, use Index Array to extract columns 1 and 2 from each array row.  Use the equal function to compare.  Use an AND function to combine the comparisons into one boolean.  Feed that boolean into a case structure.  You will need a shift register initialized by an empty 2D array to produce Array 3.  Inside the case, if True, make a 1D array with Array 1 entire row and Array 2 last column.  Feed this into the shift register.  If false, just pass the shift register through unchanged.

[Fro2]

Thanks for the prompt reply tbob,

Your solution was close but I could not figure out the portion wehere you wrote "Inside the case, if True, make a 1D array with Array 1 entire row and Array 2 last column."  I tried the build array and really could not get it to work.  I have attached "tbob attempt.vi".   Also as you mentioned the arrays must be the same size,  In my test they are not the same size but the smallest size IS what I wish to ultimealy use.  It appears your code is comparing Array 1's row one with Array 2's row one thus each row with each other.  I think what I need is to search for the contents of the rows in Array 2 looking for a match to the row in Array 1 based on the date and time upon a match I need to get all the associated rows in Array 2 appended with Array 1.  I have attached a VI eintieled "myattempt.vi"

[Rodrigo_Cuenca]

Here is my solution, it's like tbob says just that I'm searching rather than assume that the arrays are ordered and with the same indexes.

[tbob]

Rodrigo beat me to it.  Here is my code.  It does the same thing.  I didn't use search because it will find the first instance and then stop, and you have to account for that by continuing the search.  So I used auto indexing to look for a match with each element.

[Darin.K · Accepted solution]

Same idea, different implementation using variants to perform the searching.

[Fro2]

Thank you all for your suggestions.

Using a small data set all of three versions of the code worked even when the data was out of order or did not have the same number of records.  For the various versions of the code I was able to swap the inputs and the code still worked except for MatchEntries.vi.  I modified the code to obtain the two arrays from a file, one file had 159,226 records “ARRAY1” and the other file had 40,357 records “ARRAY2”, hence why I was not going perform the routine manually in Excel or by writing it down by hand.  Since the files were so large none of the three versions was able to process the data on my computer “Memory Full”.  Using a smaller data set, 38,700 records for ARRAY1 and 9,616 records for ARRAY2, which is all I really needed I was able to process the data correctly with two versions of the code.  For comparison purposes, I processed the same data set three times with each version of the code. The code “Array Match.vi” written by tBob took an average of 132 seconds to process the files and the code “MatchArrays.vi” written by DarinK took an average of 115 seconds to process the files.  I decided to use DarinK’s code.  Sorry to say the code “MatchEntries.vi” written by Rodrigo could not process the data set.  I allowed the code to run for five minutes and ARRAY3 still did not have an output.  Since the code worked on a small data set when the inputs was swapped, I went ahead and swapped the inputs for this larger data set. Even once I swapped the inputs the code still did not process the data after five minutes.

DarinK, interesting approach of using variants.  I definitely need to look into this and learn more on variants thereby gain an understanding of how your code works.  As of now I have no idea.

Thanks again for everyone’s help.

[Darin.K]

Great feedback on the different approaches!  Quick testing on a couple of hand-typed arrays is no match for some real world exposure.

Now that I have whet your appetite, I will explain a little bit about what is going on.  First the basic idea is that variants have attributes along with their usual datatype and value.  Attributes are in the form of name/value.  This in itself makes them useful for storing data based on a key.  In addition, the method used to store and retrieve this data is very efficient.

The first For Loop takes Array2 and converts the first two columns into a single string col1-tab-col2.  This becomes the attribute name, and the remaining columns become the value.  The second loop starts similarly, converting the first two columns of Array1 into a single string.  Now I see if this name exists, which is where the variant's power is evident.  If it exists, I add the data from Array2 to that of Array1 and append it to the final result Array3.

Knowing the size of your data, I would probably try a few different things, but if you are able to break the problem down into large but manageable chunks, it should work.

Suggestions for further reading:

http://forums.ni.com/t5/LabVIEW/Darren-s-Weekly-Nugget-10-09-2006/m-p/425269

And given the size of your data:

http://zone.ni.com/devzone/cda/tut/p/id/3625

A bit dated (hint, hint NI) but still full of useful info.

[tbob]

Congrats Darrin.  This is the second time that you have showed us the power of Variant Attributes.  I can't remember the other thread, but I was impressed by both examples.  Now that you have explained the concept of Name/Value (very much like a config file's key=value), I will make use of this feature to make my code more efficient when possible.  Thanx for the interesting tip.  Hey, write a nugget on this.

[Darin.K]

And now for my Regex solution.....Just Kidding. maybe...

Until the nugget appears, I will just mention the following:

I may be accused of overusing this (or Search and Replace, or..) but one reason is to raise awareness.  The answer may not always, or may rarely be yes, but you should always ask "Should I use Variant Attributes (or regexes) to do this?" When the answer is yes, the method is simple and powerful.

This example, and the other recent one both involve searching arrays of strings.  This is right in the wheelhouse to take advantage of the Red/Black tree used to store attributes.  You can always Flatten to String for other datatypes, which is effective, but when I want to demonstrate the beauty of the method I like examples where there are fewer distractions.

As I often state, I started using this method because it looked nice and cleaned up my code, and to be honest, a little of the "I am using new and obscure objects, look how clever I am".  I thought of it as a free lunch.  Through experience, I knew it was efficient, but when I learned just how efficient, it was no longer a free lunch, it was like someone was paying me to have lunch.

Finally, let me plug the following idea:

http://forums.ni.com/t5/LabVIEW-Idea-Exchange/Associative-Arrays/idi-p/1104538