Dynamic JSL Tree Box Creation from Hierarchical Data

[#1]

Can someone suggest a method of creating a dynamic nested tree box using Hierarchical Data. I tried for a couple of hours tonight and it is confounding me  

I have some dummy data below to depict the relationships which is three levels deep. Obviously I don't want to hard code this as I will not know up front how many level of 'nesting' I will need to make. In reality it will be 10 or more levels. 

Cheers, Troy

This is what I'm starting with (Table attached)

This is what I want to generate

[#2]

Here is something I put together using your sample table, that might help you get to where you want

Names Default To Here( 1 );
dt = Current Data Table();
maxLevels = Col Max( :EMPLEVEL );
dt << select where( :EMPLEVEL == 1 );
dtJoined = dt << subset( columns( :EMP_ID, :FULL_NAME ), selected rows( 1 ) );
For( m = 1, m <= N Cols( dtJoined ), m++,
	Column( dtJoined, m ) << set name( Column( dtJoined, m ) << get name || "1" )
);
For( i = 2, i <= maxLevels, i++,
	dt << select where( :EMPLEVEL == i );
	dtTemp = dt << subset( columns( :EMP_ID, :FULL_NAME, :MGR_ID ), selected rows( 1 ) );
	For( m = 1, m <= N Cols( dtTemp ), m++,
		Column( dtTemp, m ) << set name( Column( dtTemp, m ) << get name || Char( i ) )
	);
	Eval(
		Substitute(
				Expr(
					dtTemp2 = dtJoined << Join(
						With( dtTemp ),
						Merge Same Name Columns,
						Match Flag( 0 ),
						By Matching Columns( __emp__ = __mgr__ ),
						Drop multiples( 0, 0 ),
						Include Nonmatches( 0, 0 ),
						Preserve main table order( 1 )
					)
				),
			Expr( __emp__ ), parse(":EMP_ID" || Char( i - 1 )),
			Expr( __mgr__ ), Parse(":MGR_ID" || Char( i ))
		)
	);
	Close( dtTemp, nosave );
	Close( dtJoined, nosave );
	dtJoined = dtTemp2;
);

theNameList={};
for(i=1,i<=maxLevels,i++,
	insert into(theNameList, parse(":FULL_NAME" || char(i)))
);

Tabulate(
	Show Control Panel( 0 ),
	Add Table(
		Row Table( Grouping Columns( eval(theNameList) ) )
	)
)

[#3]

You might get some ideas from here Directory Tree: Explore Space Used by Folders or possibly from file-indexer I have been working on jthi-jmp-tools/file-indexer (github.com) (see bin/bin/DirectoryTreeHandler.jsl, it isn't yet as generic as I would like it to be, but hopefully it will get there), my code is currently a mess, because I haven't had time to finish it and start refactoring.

In my code the (current) input is datatable like this:

and current end result something like this:

I modified the input directory list to include one much deeper directory and it seemed to have no issues. I think magic word here might be recursion

Hopefully the function get_directory_roots can give you some ideas.

Edit:

JSL Function List addin can also provide some ideas, I think it didn't have more than 1 or two levels

[#4 · Accepted solution]

I think that this fits your needs as I understand them:

Names Default to Here( 1 );
dt = Data Table( "DummyTable" );
relations = [=>];
For Each Row( dt,
	relations[:FULL_NAME] = Tree Node( :FULL_NAME );
	relations[:FULL_NAME] <<Set Data( Eval List( {:FULL_NAME, :EMP_ID, :MANAGER_NAME, :MGR_ID, :EMPLEVEL} ) );
);
roots = {};
For Each Row( dt,
	If( :MANAGER_NAME != "" & relations << Contains( :MANAGER_NAME ),
		relations[:MANAGER_NAME] << Append( relations[:FULL_NAME] )
	,
		roots[N Items( roots ) + 1] = relations[:FULL_NAME]
	);
);

New Window( "TreeBox Tests", tree = Tree Box( roots, Size( 300, 500 ) ) );

key = relations << First;
While( !Is Empty( key ),
	Try( tree << Expand( relations[key] ) );
	key = relations << Next( key )
);
tree << Set Node Select Script(
	Function( {this, node},
		Print( node << Get Data )
	)
);

[#5]

@ErraticAttack 

Great solution!

[#6]

Yes, I agree. Very elegant solution. I was headed down the road of tracking parent/child relationships and things were starting to get messy.

Thanks all for the replies, many excellent snippets of code.

- Troy

[#7]

seems that the code got truncated

[#8]

The fishbone diagrams have a right-click menu to rearrange them. They all use the same displaybox tree. It is possible to add other controls to the tree, similar to the label. The platform is hiding under Analyze->QualityAndProcess->Diagram.

dt = Open("/Z:/DummyTable.csv");
diagram = dt << diagram(parent(MGR_ID),child(EMP_ID),label(FULL_NAME));
// fishbone is the default, change it like this:
(report(diagram)[hierbox(1)])<<changetype(
    Fishbone
    //Hierarchy
    //Nested
)

Nested

FishboneHierarchy

edit: it looks like the diagram platform may not be able to reopen a closed node in JMP 15/16 (via popup menu). In JMP 14 it can. I've reported the issue.

[#9]

Thanks for reporting the issue with the inability to reopen a closed node, @Craige_Hales. The bug has been addressed in JMP 17.