Hi,
I am using a script from the following post to try and expand on the heatmap that is created from the script. The script in its current form does a heatmap for all of the columns together but I would like a separate heatmap PER column. In other words I would like to find the min, mid and max for a certain column then apply a heat map only on that column then do the same thing for the next column and continue.
Attached are the following
1. sample_base_table.jmp: This is sample set of data that is being extracted from.
2. extract_cols.jsl: this it the script that summarizes the data to be used for the heatmap script
3. heatmap.jsl: this script does the heatmap for the specified columns
Issues:
When I run extract_cols.jsl I get 4 new datatables named "Untitled X". Then when I run heatmap.jsl it only is able to create a heat map for the last Untitled X table created. I don't understand why this is... When I look the debugger code it says "could not find column in access or evaluation of 'Column' ". However, I print the column being used "Median(Values, Mango)" and it shows up so I am not sure what is happening (see output picture below).
Improvements/Future Work:
Ideally after I can figure out why only last data table created is updated I would like to concatenate all of these 4 heatmap columns of fruits into one data table that also has the Countries and fruit info contained as well. In other words I am generating 4 (32x3) tables and I would like to generate 1 (6x32) table (4 fruit heatmap + countries column + locations column). Currently the way I am doing is with JMP Query Builder to do the column concatenation.
If you hover over trans:theList or if you specify
show(trans:theList);
in your script it will show you what is in the list. What you will see are 5 entries. The first one will be
Data Table( 'the name of your initial data table");
Each entry after that will be the pointer to the data table filter by, Apple, then Banana, then Mango, and finally Orange.
It does not contain the data, just a pointer to it. The column names, etc. are retrieved from the actual data table.
You need to take the time to read the Scripting Guide available from the JMP Documentation Library, under the Help pull down menu.
Thanks that fixed it!
So when I run the heatmap script I get the message below. When I change
dt= trans:thelist[5]
it works but again only for the last data table created. Also this namespace concept is new to me. Let me try to summarize what this is doing and can you confirm if my understanding is correct? So by using namespace in the script we are able to saving all the data table names (and more importantly the column names and value of that data table) into a list trans which we then call in the heatmap script.
my error, change
insert into(theList,dt);
to
insert into(trans:theList,dt);
theList does not exist......but trans:theList does
Thanks for the input!
When I run the updated extract_cols script you provided I get the following error message below about L-value access...
You are specifying
Names Default To Here( 1 );
which creates a separate memory namespace for that specific script. That is, in your extract_cols script, there is a list named "fruit". If in your heatmap script you specified
myFruit = fruit[1];
your heatmap script would not know anything about your variable called fruit.
In your heatmap script, you set the variable "dt" equal to the current data table. From your description, you are running the headmap script right after running the extract_cols script. At any given time in JMP, there is only 1 data table that is the current active data table. Therefore when you specify
dt = Current Data Table();
the heatmap script gets the current active data table, which would be the last data table that you created in the extract_cols script
If you need to pass the original data table reference to the second script, and possibly the other 4 data table references, one way of doing this is to create a separate namespace and to place the references into that namespace. Here is a modification to your script creates the namespace and places the data table references into it.
Names Default To Here( 1 );
Clear Symbols();
dt = Current Data Table();
if(isnamespace("trans")==0, trans = new namespace("trans"));
trans:theList = {};
insert into(theList,dt);
fruit = {"Apple","Banana","Mango","Orange"};
For(i = 1, i <= N Items(fruit), i++,
obj = dt << Tabulate(
Set Format( Uniform Format( 10, 0 ) ),
Add Table(
Column Table(
Statistics( Median ),
Grouping Columns( :fruits ),
Analysis Columns( :Values )
),
Row Table( Grouping Columns( :Countries, :locations ) )
),
Local Data Filter(
Add Filter(
columns( :fruits),
Where( :fruits == fruit[i] ),
Display( :fruits, N Items( 14 ) )
)
)
);
trans:theList[i+1] = obj<< make into data table;
Current Report() << Close Window
);
Then you can reference the namespace in your heatmap script to get the primary table reference or any of the data table references that were saved in the namespace.
Names Default To Here( 1 );
Clear Symbols();
dt = trans:thelist[1];
dt << Begin Data Update;
///////////////////////////////////
// Setting Variables for script ///
//rev_scale = "Reverse Scale";
rev_scale =0;
mean_median = 1; //mean=0, median=1
print(mean_median);
///////////////////////////////////
//gets all columns that are continous and stores that in a list as strings.
colList = dt << get column names( continuous, string );
print(colList);
// Create a temporary subset where the columns are the column list, selects all rows,
// invisible makes it so you don't see the datatable on screen since it is temporary
dtsub = dt << subset(columns(colList), selected rows(0),invisible);
// Move the data into a matrix this will be used later to get min, max, median|mean
allData = dtsub << get as matrix;
// Close the no longer needed temporary data table
close(dtsub, nosave);
// Calculate the statistics across the full data matrix
myMax = Maximum(allData);
myMin = Minimum(allData);
if(mean_median == 0,
myMid = Mean(allData),
myMid = Median(allData)
);
print(myMax);
print(myMin);
print(myMid);
print(colList);
// Run for loop over data table with the min, max and median|mean calculated from above
// gradient color scheme needs to be set.
if(rev_scale == 1,
For( i = 1, i <= N Items( colList ), i++,
Eval(
Substitute(
Expr(
Column( __col__ ) <<
set property(
"color gradient",
{"Green Yellow Red",
Range( {__Max__, __Min__, __Mid__} ),"Reverse Scale"}
) << Color Cell by Value( 1 );
),
Expr( __col__ ), colList[i],
Expr( __Max__ ), myMax,
Expr( __Min__ ), myMin,
Expr( __Mid__ ), myMid
)
);
);
,
For( i = 1, i <= N Items( colList ), i++,
Eval(
Substitute(
Expr(
Column( __col__ ) <<
//Column( colList[i] ) <<
set property(
"color gradient",
{"Green Yellow Red",
Range( {__Max__, __Min__, __Mid__} )}
) << Color Cell by Value( 1 );
),
Expr( __col__ ), colList[i],
Expr( __Max__ ), myMax,
Expr( __Min__ ), myMin,
Expr( __Mid__ ), myMid
)
);
);
);
