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

ScriptingRe: 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

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

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

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