Okay I have a very specific request and hopefully it won't trouble you guys too much, I'm very new to JMP so would like some help working this out. Seems like it should be relatively easy (relative because if were very easy I might have already figured it out 
.
Task: Open Excel document and import data (default settings work just fine for this particular data string, at least for what I need out of it)
- One column is Time[sec], the rest is data in that time frame. Only concerned at the moment with data in the Ch1-Ch8 columns. And time of course, but that's just for the graph.
- Once the data is imported I would like to have a prompt ask me which columns to graph (CH1-CH8) followed by a prompt for each channel asking what the chemical Offset (scale in this case, just referred to as offset by jargon. Column value times specific number) is for the chemical in question, or even just have a menu to select chemical from a preset list.
- Once that is completed it should be fairly simple to get the graph to show the specified channels with the 'offset' calculated, As well as the peak values.
Known hangups;
- importing multiple files from excel seems to not be as intelligent as individual imports, if we can specify what certain columns are in the import process this might be eliminated as we know that time[sec] and CH1-CH8 are numeric/continuous.
Like I said, it seems like it should be reasonable, I'd beat my head against it for a bit but it really would have been useful yesterday, so hopefully someone will be willing to help expedite things for any future needs, thanks in advance!
Thanks! That is very workable, only took a few minutes to figure out what I needed to edit so it would do what I want, possible I might be able to make it more efficient at some point once I understand the script better, but this certainly does what I want it to do!
dt = Open();
col_list = dt << get column names( string );
Remove From( col_list, 1 ); // Remove time[sec] from list
cols_to_plot = {};
offset_list = {};
offset_cols = {};
// Define the graph builder commands in an expression for use by the OK button
graph_expr = Expr(
For( i = 1, i <= N Items( cols_to_plot ), i++,
new_name = cols_to_plot[i] || "-Offset";
Insert Into( offset_cols, new_name );
dt << New Column( new_name,
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula( As Column( dt, cols_to_plot[i] ) * offset_list[i] )
);
);
// Now for the graph - build a text string and then execute it
gb_text = "\[gb = graph builder(
show control panel(0),
variables(
X( :Name( "Time[sec]" ) ),]\";
elements_txt = " ),
Elements( Line( X, ";
For( i = 1, i <= N Items( cols_to_plot ), i++,
If( i == 1,
// then
gb_text = gb_text || Eval Insert( "\!N\[ Y( :Name( "^offset_cols[i]^" ) ), ]\" )
,
// else
gb_text = gb_text || Eval Insert( "\!N\[ Y( :Name( "^offset_cols[i]^" ), Position( 1 ) ),]\" )
);
elements_txt = elements_txt || Eval Insert( "Y ( ^i^ ), " );
);
elements_txt = elements_txt || "Legend( 7 ) ) ,";
gb_text = gb_text || elements_txt || ") )";
Eval( Parse( gb_text ) );
);
nw = New Window( "Graph Chemicals",
Panel Box( "Select channels to graph",
Lineup Box( N Col( 2 ),
Text Box( "Column" ),
Text Box( "Offset" ),
cb1 = Check Box( "Ch1" ),
ch1_neb = Number Edit Box(),
cb2 = Check Box( "Ch2" ),
ch2_neb = Number Edit Box(),
cb3 = Check Box( "Ch3" ),
ch3_neb = Number Edit Box(),
cb4 = Check Box( "Ch4" ),
ch4_neb = Number Edit Box(),
cb5 = Check Box( "Ch5" ),
ch5_neb = Number Edit Box(),
cb6 = Check Box( "Ch6" ),
ch6_neb = Number Edit Box(),
cb7 = Check Box( "Ch7" ),
ch7_neb = Number Edit Box(),
cb8 = Check Box( "Ch8" ),
ch8_neb = Number Edit Box(),
),
),
Panel Box( "Actions",
H List Box(
okb = Button Box( "OK",
If( cb1 << get( 1 ),
Insert Into( cols_to_plot, col_list[5] );
offset_value = ch1_neb << get;
Insert Into( offset_list, offset_value );
);
If( cb2 << get( 1 ),
Insert Into( cols_to_plot, col_list[6] );
offset_value = ch2_neb << get;
Insert Into( offset_list, offset_value );
);
If( cb3 << get( 1 ),
Insert Into( cols_to_plot, col_list[7] );
offset_value = ch3_neb << get;
Insert Into( offset_list, offset_value );
);
If( cb4 << get( 1 ),
Insert Into( cols_to_plot, col_list[8] );
offset_value = ch4_neb << get;
Insert Into( offset_list, offset_value );
);
If( cb5 << get( 1 ),
Insert Into( cols_to_plot, col_list[9] );
offset_value = ch5_neb << get;
Insert Into( offset_list, offset_value );
);
If( cb6 << get( 1 ),
Insert Into( cols_to_plot, col_list[10] );
offset_value = ch6_neb << get;
Insert Into( offset_list, offset_value );
);
If( cb7 << get( 1 ),
Insert Into( cols_to_plot, col_list[11] );
offset_value = ch7_neb << get;
Insert Into( offset_list, offset_value );
);
If( cb8 << get( 1 ),
Insert Into( cols_to_plot, col_list[12] );
offset_value = ch8_neb << get;
Insert Into( offset_list, offset_value );
);
graph_expr;
nw << close window;
),
canb = Button Box( "Cancel", nw << close window ),
)
),
);
// Pre-check checkboxes if column exists
For( i = 1, i <= N Items( col_list ), i++,
If(
col_list[i] == "Ch1", cb1 << set( 1, 1 ),
col_list[i] == "Ch2", cb2 << set( 1, 1 ),
col_list[i] == "Ch3", cb3 << set( 1, 1 ),
col_list[i] == "Ch4", cb4 << set( 1, 1 ),
col_list[i] == "Ch5", cb4 << set( 1, 1 ),
col_list[i] == "Ch6", cb4 << set( 1, 1 ),
col_list[i] == "Ch7", cb4 << set( 1, 1 ),
col_list[i] == "Ch8", cb4 << set( 1, 1 ),
)
);
There's a bunch of raw data lines that are unecessary for what we do with the data, so it puts the new modified lines much later in the spreadsheet, but it'll be easy enough to work with for now, thanks very much!
This should get you started. Some advanced techniques in here, especially the dynamic creation of the graph.
dt = New Table( "chemical", Add Rows( 10 ),
New Column( "Time[sec]", Numeric, "Continuous", Format( "Best", 12 ),
Set Values( [1, 2, 3, 4, 5, 6, 7, 8, 9, 10] ) ),
New Column( "Ch1", Numeric, "Continuous", Format( "Best", 12 ),
Set Values(
[0.405479144770652, 0.96118725137785, 0.184666735585779,
0.0779315158724785, 0.27096574800089, 0.274306696141139,
0.499865497928113, 0.645765431923792, 0.850604736013338,
0.375170755432919] ) ),
New Column( "Ch2", Numeric, "Continuous", Format( "Best", 12 ),
Set Values(
[-1.49768617758238, 0.627178362585814, 0.444456848007608,
0.23009213128918, 0.191183820733896, -0.871513987372999,
0.117414289530274, 0.673226666360769, 2.21437748894808,
-0.757598019757669] ) ),
New Column( "Ch3", Numeric, "Continuous", Format( "Best", 12 ),
Set Values(
[0.58753531659022, 0.722538525937125, 0.305761067429557,
0.53906583529897, 0.635318850399926, 0.701098019722849,
0.746448054211214, 0.526889791944995, 0.749249363318086,
0.959693510085344] ) )
);
col_list = dt << get column names(string);
remove from(col_list, 1); // Remove time[sec] from list
cols_to_plot = {};
offset_list = {};
offset_cols = {};
// Define the graph builder commands in an expression for use by the OK button
graph_expr = expr(
for (i = 1, i <= nitems(cols_to_plot), i++,
new_name = cols_to_plot[i] || "-Offset";
insertinto(offset_cols, new_name);
dt << new column(new_name, Numeric, "Continuous", Format( "Best", 12 ),
Formula( as column(dt, cols_to_plot[i]) * offset_list[i] ) );
);
// Now for the graph - build a text string and then execute it
gb_text =
"\[gb = graph builder(
show control panel(0),
variables(
X( :Name( "Time[sec]" ) ),]\";
elements_txt = " ),
Elements( Line( X, ";
for (i = 1, i <= nitems(cols_to_plot), i++,
if (i == 1,
// then
gb_text = gb_text || evalinsert("\!N\[ Y( :Name( "^offset_cols[i]^" ) ), ]\");
,
// else
gb_text = gb_text || evalinsert("\!N\[ Y( :Name( "^offset_cols[i]^" ), Position( 1 ) ),]\");
);
elements_txt = elements_txt || evalinsert("Y ( ^i^ ), ");
);
elements_txt = elements_txt || "Legend( 7 ) ) ,";
gb_text = gb_text || elements_txt || ") )";
eval(parse(gb_text));
);
nw = new window("Graph Chemicals",
panel box("Select channels to graph",
lineup box(ncol(2),
text box("Column"), text box("Offset"),
cb1 = checkbox("Ch1"), ch1_neb = number edit box(),
cb2 = checkbox("Ch2"), ch2_neb = number edit box(),
cb3 = checkbox("Ch3"), ch3_neb = number edit box(),
cb4 = checkbox("Ch4"), ch4_neb = number edit box(),
),
),
panelbox("Actions",
hlistbox(
okb = button box("OK",
if (cb1 << get(1),
insertinto(cols_to_plot, col_list[1]);
offset_value = ch1_neb << get;
insertinto(offset_list, offset_value);
);
if (cb2 << get(1),
insertinto(cols_to_plot, col_list[2]);
offset_value = ch2_neb << get;
insertinto(offset_list, offset_value);
);
if (cb3 << get(1),
insertinto(cols_to_plot, col_list[3]);
offset_value = ch3_neb << get;
insertinto(offset_list, offset_value);
);
if (cb4 << get(1),
insertinto(cols_to_plot, col_list[4]);
offset_value = ch4_neb << get;
insertinto(offset_list, offset_value);
);
graph_expr;
nw << close window;
),
canb = button box("Cancel", nw << close window;
),
)
),
);
// Pre-check checkboxes if column exists
for (i = 1, i <= nitems(col_list), i++,
if (col_list[i] == "Ch1", cb1 << set(1, 1),
col_list[i] == "Ch2", cb2 << set(1, 1),
col_list[i] == "Ch3", cb3 << set(1, 1),
col_list[i] == "Ch4", cb4 << set(1, 1),
);
);
JMP 14 is what I have, and yes, Ch1 etc... is the actual column names. I discovered the scale/offset tab and can use that for manually entering in the chemical offset, but I'm looking to try and automate the process so that doesn't need to be done every time for every chemical on every individual channel.
Graph type is a line, time is the X value and the channel(s) are the Y value.
I'm trying to figure out how to automate it using JSL but I'm still really new with the interface and some things that might be intuitive aren't clicking yet. The Excel file is generated automatically from another program, so is always in the same format. It shouldn't be terribly difficult to script it to look for the specified columns, I am simply not familiar with the scripting language yet. I'm hoping maybe somebody might already have a script that does something similar I can edit to my needs, I can be fairly handy at picking a script apart and making it do what I want, just not that great at writing one from scratch.
Thanks for the information!
Damion,
You did not mention what version of JMP you are using, and which JMP graph/platform you would use. Also does the first row in each of these columns store the chemical name or are the names just Ch1, Ch2, ..., Ch8
JMP has a fairly standard interface for the graphs and analyses. I am assuming you might use Fit Y by X, where Y are the chemicals (Ch1-Ch8) and X is Time[sec]. Before adding each chemical, right click, and select Transform > Scale Offset , see the screen shot. Use 1 for the multiplier, and specify the offset. Use the transformed chemical for Y.
This is not an automatic prompt, but a method to do this via the user interface (UI).
Of course, your task can be automated and tailored to your specific needs using teh JMP Scripting Language (JSL).
Regarding your request to read in only selected Excel columns, there are multiple methods to do that, but it depends upon how your excel file is setup, or if you have an ODBC connection to read Excel as a data base etc. If you can provide a dummy excel sheet you might get a more specific response.
