438466_en-US

liliconcom

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

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

Automation and ScriptingData Blending and CleanupMass CustomizationRe: Update Column Axis Reference Line based on data from another data table

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></REMOVE>    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.
 );
);

 

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

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.
 );
);

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

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

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

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) 
  })));
 );
);

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

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)})));

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

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?  

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

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.

 

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

It is very helpful JSL script to import data directly into Axis. If that possible to add below label setting into JSL script above? 

 

I try to add / modify your code with additional setting come with error. 

 

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( 0 )),Expr(char(col_name_list_lookup[i])), 1, 

Label Position( "Inside Inline" ), Opposite Axis( 1 ))

)

});

 

new_properties = Insert(new_properties, new_properties_temp);

);

 

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

Make sure all your brackets are in correct position

View more...

For(i = 2, i <= N Items(col_name_list_lookup), i++,
	new_properties_temp = Eval Expr(
		{Add Ref Line(
			Expr(As Column(dtLookup, col_name_list_lookup[i])),
			"Dashed",
			Expr(Color To RGB(0)),
			Expr(Char(col_name_list_lookup[i])),
			1,
			Label Position("Inside Inline"),
			Opposite Axis(1)
		)}
	);
 
	new_properties = Insert(new_properties, new_properties_temp);
);