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