Hello,
I have a very large data table (around 6 million rows). It has in particular as columns a Date column, a Product Reference column and a Result column (which has two possible values: OK or NOK). Each row corresponds to a component test.
I would like to be able to let the user select a period or date. From this period I would like to be able to find and extract the 5 product references that have the most Results == NOK in percentage compared to the number of tests carried out over the selected period.
I don't know how to script this optimally.
Thank you in advance if you have any ideas for me
Hi, There are many ways to do this. One particularly nice way involves JMP Query Builder with a post-query script, which will prompt the user for the begin and end dates before proceeding with the analysis. I highly recommend learning about the JMP Query Builder for work like this. It is a bit too much to type here, unfortunately.
I'll give a simple alternative that still works well.
First, I always recommend storing 1/0 data numerically, as 1s and 0s, using value labels to show things like "OK" and "NOK". In your case, we could use 1 => "OK" and 0 -> "NOK". (See attached data table.)
I recommend this because summarizing a mean by groups immediately provides the proportion of "OK" in each group. If the 1s and 0s had been reversed, the mean gives the proportion of "NOK".
So, with that change made, we do one more thing: we add table variables. Right-click on the red-triangle near the table name, and select New Table Variable. This shows a dialog where you can name a new table variable and give it an initial value.
Doing this for "Begin" and "End" allows us to define two values (strings, in this case, but numbers work, too) that can be used just like columns in formulas and scripts.
Using the same red triangle menu, we can add a new script to the data table. I've added a script called "Worst 5" and pasted the text of the script below.
Names Default To Here(1);
dt = Current Data Table();
//change table variable strings to times. the format used for the string must be one that JMP recognizes.
beg = informat(dt:Begin);
end = informat(dt:End);
//subset the table to get only those rows in the time interval of interest
dtSub = dt << subset(selected Columns (0), rows(dt << get rows where (beg < :date < end)));
//summarize this new table to find averages of the OK column, grouping by product. This will be the proportion of "good"
dtSubSum = dtSub << summary(group(:product), mean(:OK));
//sort this new table, ascending
dtSubSum << sort(replace table, by(:"Mean(OK)"n), order(ascending));
//the first 5 rows now have the lowest proportion of "good", hence highest proportion of "bad"
dtSubSum << subset(selected Columns(0), rows(1::5));
//close the first subset table, and the summary table linked to it.
close(dtSub, nosave);
Now it is easy to get the report:
1) Change the Begin and End values as desired.
2) Press the green button on the "Worst 5" script to get the results.
Again, there are slicker ways to do this, but they require knowledge of either Query Builder, or interactive window (Display Tree) scripting. I recommend learning both of those highly, but in the meantime there is much that can be done with table variables and basic table operations.
Another option, involving no scripting:
1) Use the Tabulate platform to compute the means, also using a local data filter to limit the date range considered
2) Right-click on the tabulate table and select Make into Data Table, then sort the table so the 5 "worst" rows (i.e., the 5 lowest OK averages) are at the top.
3) If desired, select these 5 rows and subset.
Hopefully one of these ideas will get you started.
Cheers,
Brady
