Creating and Comparing Groups - Associative Arrays?

[#1]

Good Morning!! I have been banging my head against the wall for a couple of weeks now to try to figure this one out, but I have run out of ideas.

I would like to group all Prime/Alternates on a Build List together into groups that I could then compare to all other Prime/Alternates on other Build Lists and make a recommendation to add Alternates to other Part Numbers based on the Prime that is used.

Attached is a sample file. For simplicity, I’ve made all Prime/Alternate groups start with the same number (i.e. 20 or 40), but the last digits are different. Initial groups of Part Numbers can be formed by looking at Assembly and Location, and then add groups to others if they have any commonality. I was trying to use Associative Arrays, but I’m not sure if that is the right approach.

Thank you all in advance for any help you can offer!

[#2]

Your explanation is confusing to me.  Would it be possible to provide a small sample of what you are expecting the output to be?

[#3]

Sorry for the confusing explanation. I hope that this clarifies it a bit: At the very end, I would like to see the following group {"20-0002", "20-0003", "20-0004", "20-0005", "20-0007”} that I can compare to all other Assemblies. For example, the 20-0007 part number is not called out on KBR175, TRT330, or KNW995 and want to recommend that as an alternative.

[#4]

Do you want something like this, where you can see what Assembly's have not been used for specific Part Numbers?

[#5]

Thanks @txnelson!  I like this approach, but not all of my assemblies can be built with the same Part Numbers and it would be difficult to look at thousands of assemblies and parts.  I probably simplified my example too much and added a very complex explanation on top of it.

I made some progress on an approach that I think will work.  I was able to figure out how to dynamically create associative arrays for every Assembly-Location and the Part Numbers that go with it.  Here is the script:

dt=Data Table( "Example" );

//Combine columns to get a single unique identifer for each row
dt << Combine Columns(
	delimiter( "_" ),
	Columns( :Assembly, :Location ),
	Selected Columns are Indicator Columns( 0 ),
	Column Name( "Assembly_Location" )
);

//Creates columns for each Assembly_Location - Grouping Like Part numbers together
dt1=dt << Split(
	Split By( :Assembly_Location ),
	Split( :Part Number ),
	Remaining Columns( Drop All ),
	Sort by Column Property, 
        Table Name ("Transposed Example")
);

//Get all column names as a list
allnames=dt1<<get column names(string);

//Create an associative array for every column with the column name used as the array name
for (i = 1, i <= nitems(allnames), i++,

// If it's a string column then create the column name and an array name for use as a dynamic input 
     if (is string(allnames[i]),
            col_name = "dt1:Name(\!"" || allnames[i]||"\!")" ;
			array_name = "aa" || allnames[i];

// Build associative array string dynamically and then execute it.
        str = evalinsert(
			"\[^array_name^=Associative Array (^col_name^) ;]\");
        eval(parse(str));
);
);

aaKBR175_001;
aaKBR175_002;
aaKBR175_003;

Now I want to compare each array to the others and see if there is any overlap.  If there is, I want to combine the arrays. For Example, looking at KBR175-001 (20-0002, 20-0003, 20-0004, 20-0005), TRT330-003 (20-0002, 20-0003, 20-0004), NTE484-001 (20-0002, 20-0003, 20-0005, 20-0007), KNW995-002 (20-0002, 20-0003, 20-0004, 20-0005). The result would be: (20-0002, 20-0003, 20-0004, 20-0005, 20-0007).

I thought I could use the intersect operation, but that replaces the first array with the results of the intersection:

//Creates an Associative Array for each two columns
aa1=Associative Array (dt1:Name("KBR175_001") );
aa3=Associative Array (dt1:Name("NTE484_001") );
//Looks for overlap
aa1 << intersect ( aa3 );
Show(N Items(aa1));
//If there is overlap, it combines the two Arrays into one, otherwise it returns "no"
if (N Items (aa1)>0, aa1<< Insert( aa3 ), Show("no"));

But this only gives me the intersection of aa1 and aa3 plus aa3 or {"20-0002", "20-0003", "20-0005", "20-0007"}  I have excluded "20-0004" which was originally part of aa1. 

My question is two part:

1. Is there a way to compare arrays and then combine both arrays to include all keys?

2. How would I compare all arrays to all other arrays? I assume some sort of For Loop.  

[#6]

@SirBWhite you pose an interesting problem, and I expect there are elegant methods to do this. Jim's, @txnelson's, suggestion got me thinking about bubble sorts and similarity type analyses.  I expect there are some text mining solutions and text distance metrics that would make this an easy task. 

Based upon your discussion with Jim, I would reorganize the data:

• Split the table, and 
• Sort it

The resulting table (a portion show below) would reduce the selection of comparable assemblies dramatically.

Here is a graphical picture of this table

The script to do this attached.  What I would do is the following:

• Add a column in the original column called  Type Code that is 2 when Type = PRM and 1 when Type= ALT. 
• Then split the table (temp) and recode missing with zero
• I would still sort the table, but this is unnecessary.

This table with the parts columns now being numeric columns can be used in cluster algorithms and nearest neighbor algorithms.

[#7 · Accepted solution]

Thank you all for your input!  I shared the problem with a collegue and he came up with an alternate approach - More of a brute force approach.  

Attached is the script that does what I'm looking for.  Basically, we join the table on itself to get part numbers into groups and then joined again on the assembly_location to get all of their part numbers.  

After some summarizing and transposing back and forth the result is a list that has all Alternate/Prime groups in single column labeled by a grouping value. 

Thanks again!

Brian