Update Column Axis Reference Line based on data from another data table

[#1]

I'm looking to add column reference lines based on data from another data able. An example, using "Big Class" would update columns "weight' and "height" with P50 and P95 values from a lookup data table.

The rows on the lookup data table correspond to the column names in the main table. The reference lines would need to assign the label value from the lookup table column name "P50" or "P95" and the value is from the row in that column.  

Example showing the added column axis reference lines:

Example for Lookup data table:

I'm not very well versed in JSL so I appreciate any help that comes my way on this topic. I've created a script but it's not doing what I want.

It seems to corrupt the column, sometimes when plotting a distribution it is showing all '0' values. Also, if I'm trying to add both P50 and P95 ref lines, only the last one is saved to the column and it saves it on both columns.

dt = Open ("C:\Program Files\SAS\JMP\15\Samples\Data\big class.jmp");
dtLookup = New Table( "Lookup Table",
	Add Rows( 2 ),
	New Column( "Column Name",
		Character,
		Nominal,
		Set Values( {"weight", "height"} )
	),
	New Column( "P50",
		"Continuous",
		Set Values( [100, 61] )
	),
	New Column( "P95",
		"Continuous",
		Set Values( [120, 70] )
	)
);

col_name_list = dt << get column names(string);
col_name_list_lookup = dtLookup << get column names(string);
 
numRowsLookup = N Rows( dtLookup );
numColsLookup = N Cols( dtLookup );

for(n=1,n<=numRowsLookup,n++,
	col_name = dtLookup[n,1];
	If(contains(col_name_list,col_name),
		for(j=2,j<=numColsLookup,j++,
		col = Column(dt, col_name);
		col_name_Lookup = col_name_list_lookup[j];
		value = Column(dtLookup,col_name_Lookup)[n];
		col <<
			Set Property("Axis", 
			{
				//Add Ref Line( 63, "Dashed", "Orange", "P50", 2 )
				Add Ref Line( value, "Dashed", "Orange", col_name_Lookup, 2 )
			});
		);
	);
);

Thank you,

Roger

[#2]

Here is an example script. My guess is that the Axis gets overridden if you try to do it multiple times like that, so you might have to add them in same << Set Property (multiple different ways of building the expression to do it).

Names Default To Here(1);

dt = Open("$SAMPLE_DATA/Big Class.jmp");
dtLookup = New Table("Lookup Table",
	Add Rows(2),
	New Column("Column Name", Character, Nominal, Set Values({"weight", "height"})),
	New Column("P50", "Continuous", Set Values([100, 61])),
	New Column("P95", "Continuous", Set Values([120, 70]))
);

For Each Row(dtLookup,
	col_name  =:"Column Name"n;
	//because we know we have only two lines to add and they are always added
	Eval(EvalExpr(Column(dt, col_name) << Set Property("Axis", {
		Add Ref Line(Expr(:P50), "Dashed", "Orange", "P50", 2),
		Add Ref Line(Expr(:P95), "Dashed", "Orange", "P95", 2)	
		}))
	);
);

Not sure if Eval(EvalExpr()) is necessary here but it wont hurt.

[#3]

Thanks Jarmo. This solution is much simpler but it seems to overwrite any existing axis ref lines. Do you know if there is a way to preserve any Axis Ref Lines that are already present in that column?  

[#4]

I'm not 100% sure, but maybe you could first get the old Axis properties with << Get Propery("Axis") and then add to that.

Maybe something like this (there might also be more simple ways):

old_properties = Column(dt, "height") << get property("Axis");
old_with_new = Insert(old_properties, Expr(Add Ref Line(1, "Dashed", "Orange", "P45", 2)));
Eval(EvalExpr(column(dt, "height") << Set Property("Axis",{expr(old_with_new)})));

[#5]

What is the Insert() function doing here? I can't seem to it to work.

Names Default To Here(1);

dt = Open("$SAMPLE_DATA/Big Class.jmp");
dtLookup = New Table("Lookup Table",
	Add Rows(2),
	New Column("Column Name", Character, Nominal, Set Values({"weight", "height"})),
	New Column("P50", "Continuous", Set Values([100, 61])),
	New Column("P95", "Continuous", Set Values([120, 70]))
);

col_name_list = dt << get column names(string);
col_name_list_lookup = dtLookup << get column names(string);

For Each Row(dtLookup,
	col_name  =:"Column Names"n;
	If(contains(col_name_list,col_name),
		old_properties = Column(dt, col_name) << get property("Axis");
		old_with_new = Insert(old_properties, 
		{Expr(	Add Ref Line(Expr(:P50), "Dashed", "Orange", "P50", 2),
				Add Ref Line(Expr(:P95), "Dashed", "Orange", "P95", 2)
			)
		});
		Eval(EvalExpr(Column(dt, col_name) << Set Property("Axis",{
			Expr(old_with_new)	
		})));
	);
);

[#6]

You could try debugging the code in parts. You can "run" For Each Row with row by row by using Row() = rownumber instead of the loop.

//For Each Row(dtLookup,
	Row() = 1;
	col_name  =:"Column Name"n;
	If(contains(col_name_list,col_name),
		old_properties = Column(dt, col_name) << get property("Axis");
		old_with_new = Insert(old_properties, 
		{Expr(	Add Ref Line(Expr(:P50), "Dashed", "Orange", "P50", 2),
				Add Ref Line(Expr(:P95), "Dashed", "Orange", "P95", 2)
			)
		});
		Eval(EvalExpr(Column(dt, col_name) << Set Property("Axis",{
			Expr(old_with_new)	
		})));
	);
);

And then start running code row by row:

old_properties = Column(dt, col_name) << get property("Axis");
//Empty()

Some break-points for debugging (I don't use Debugger, but it would most likely be even easier with it):

Names Default To Here(1);

dt = Open("$SAMPLE_DATA/Big Class.jmp");
dtLookup = New Table("Lookup Table",
	Add Rows(2),
	New Column("Column Name", Character, Nominal, Set Values({"weight", "height"})),
	New Column("P50", "Continuous", Set Values([100, 61])),
	New Column("P95", "Continuous", Set Values([120, 70]))
);

col_name_list = dt << get column names(string);
col_name_list_lookup = dtLookup << get column names(string);

Column(dt, "weight") << Set Property("Axis", {Add Ref Line(10, "Dashed", "Red", "P10", 2)}); //debugging axis property
stop();

For Each Row(dtLookup,
	stop();
	Row() = 1;
	col_name  = Column(dtLookup, "Column Name")[Row()]; //easier to work with without For Each Row
	If(contains(col_name_list,col_name),
		old_properties = Column(dt, col_name) << get property("Axis");
		stop();
		new_properties = EvalExpr({Add Ref Line(Expr(:P50), "Dashed", "Orange", "P50", 2), Add Ref Line(Expr(:P95), "Dashed", "Orange", "P95", 2)});
		stop();
		If(!IsList(old_properties),
			prop_to_add = Name Expr(new_properties),
			prop_to_add = Insert(old_properties, new_properties);
		);
		show(prop_to_add);
		stop();
		Eval(EvalExpr(Column(dt, col_name) << Set Property("Axis",{Expr(prop_to_add)})));
	);
);

Scripting Index for insert:

Some links to assist with scripting:

JSL Syntax Reference

Scripting Guide 

JMP 16 Help

[#7]

Hi Jarmo,

I appreciate the help on this. Unfortunately I was not seeing the old and new axis properties getting updated here. I saw "prop_to_add" list gets appended with old and new properties but when I check the actual axis settings, I only saw the two new properties.

After removing the braces from the set properties, it was able to work as expected! 

Now I just need to work on making this work on N number of lookup columns.

Names Default To Here(1);

dt = Open("$SAMPLE_DATA/Big Class.jmp");
dtLookup = New Table("Lookup Table",
	Add Rows(2),
	New Column("Column Name", Character, Nominal, Set Values({"weight", "height"})),
	New Column("P50", "Continuous", Set Values([100, 61])),
	New Column("P95", "Continuous", Set Values([120, 70]))
);

col_name_list = dt << get column names(string);
col_name_list_lookup = dtLookup << get column names(string);

Column(dt, "weight") << Set Property("Axis", {Add Ref Line(10, "Dashed", "Red", "P10", 2)}); //debugging axis property
//stop();

For Each Row(dtLookup,
	//stop();
	Row() = 1;
	col_name  = Column(dtLookup, "Column Name")[Row()]; //easier to work with without For Each Row
	If(contains(col_name_list,col_name),
		old_properties = Column(dt, col_name) << get property("Axis");
		//stop();
		new_properties = EvalExpr({Add Ref Line(Expr(:P50), "Dashed", "Orange", "P50", 2), Add Ref Line(Expr(:P95), "Dashed", "Orange", "P95", 2)});
		//stop();
		If(!IsList(old_properties),
			prop_to_add = Name Expr(new_properties),
			prop_to_add = Insert(old_properties, new_properties);
		);
		show(prop_to_add);
		//stop();
		//Eval(EvalExpr(Column(dt, col_name) << Set Property("Axis",{Expr(prop_to_add)}))); // Didn't work with the braces.
		Eval(EvalExpr(Column(dt, col_name) << Set Property("Axis",expr(prop_to_add)))); //Worked when removing the braces.
	);
);

[#8]

Final update here. Special thanks to Jarmo.

I was able to get this working as intended. Final code here for future reference.

Names Default To Here(1);
namespace("here")<<remove(namespace("here")<<getkeys);

dt = Open("$SAMPLE_DATA/Big Class.jmp");
dtLookup = New Table("Lookup Table",
	Add Rows(2),
	New Column("Column Name", Character, Nominal, Set Values({"weight", "height"})),
	New Column("P50", "Continuous", Set Values([100, 61])),
	New Column("P95", "Continuous", Set Values([120, 70]))
    New Column("P99", "Continuous", Set Values([125, 75]))
);


col_name_list = dt << get column names(string);
col_name_list_lookup = dtLookup << get column names(string);


For Each Row(dtLookup,
	col_name  =:"Analysis Columns"n; //Ref column name
	new_properties = {}; // re-init
	old_properties = {}; // re-init
	If(contains(col_name_list,col_name),
		old_properties = Column(dt, col_name) << get property("Axis");
		//Add Multiple Ref Lines
		For( i = 2, i <= N Items( col_name_list_lookup ), i++,
			new_properties_temp = 
			EvalExpr({
					Add Ref Line(Expr(as column(dtLookup,col_name_list_lookup[i])), "Dashed", Expr(Color To RGB( 67 )),Expr(char(col_name_list_lookup[i])), 1)
					});
			
			new_properties = Insert(new_properties, new_properties_temp);	
		);
		//end Add Multiple 	
		
		//Add Specified Reference Lines
		//new_properties = EvalExpr({
		//		Add Ref Line(Expr(:P50), "Dashed", "Orange", "P50", 2), 
		//		Add Ref Line(Expr(:P95), "Dashed", "Orange", "P95", 2)
		//});
		
		If(!IsList(old_properties),
			prop_to_add = Name Expr(new_properties),
			prop_to_add = Insert(old_properties, new_properties);
		);
		//show(prop_to_add); // for debug
		Eval(EvalExpr(Column(dt, col_name) << Set Property("Axis",expr(prop_to_add)))); //Applies the new axis settings.
	);
);