Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Re: Creating and Comparing Groups - Associative Arrays?

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Apr 8, 2019 8:12 AM
(2696 views)

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!

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

6 REPLIES 6

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Creating and Comparing Groups - Associative Arrays?

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

Jim

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Creating and Comparing Groups - Associative Arrays?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Creating and Comparing Groups - Associative Arrays?

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

Jim

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Creating and Comparing Groups - Associative Arrays?

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.

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Creating and Comparing Groups - Associative Arrays?

@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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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