Hello, I am a user of JMP Live.
I am currently utilizing a simple function within JMP to leverage the Auto Refresh feature in JMP Live for dashboard purposes. However, while the function operates correctly on my PC, it does not work properly on JMP Live.
Even though I have disabled "Suppress Eval" before executing Data Publish, when I later download the data, the column where the function should be applied appears empty, and I can see that "Suppress Eval" is checked again in the Column Info. This issue has occurred since the recent version update.
I would appreciate it if you could investigate the cause of this issue. For reference, I am sharing the Refresh Script I am currently using.
Thank you.
//==== JMP Live Update Script
Names Default to Here(1);
dt = Current Data Table();
// Date Form Trans
Pad = function( {x}, if(x < 10, "0" || char( x ), char( x )));
FormatDatetime = function({time, pattern, prepend=""},
char(substitute(pattern,
prepend || "H", Pad(hour(time, 24)),
prepend || "M", Pad(minute(time)),
prepend || "S", Pad(second(time)),
prepend || "m", Pad(month(time)),
prepend || "d", Pad(day(time)),
prepend || "yyyy", char(year(time)),
prepend || "yy", SUBSTR(char(year(time)), 3)
))
);
// LIMS data
dt_lims = Open Database(
"ODBC:Address=96.97.28.44,14334;DRIVER={SQL Server};PWD=flatm2019!;SERVER=96.97.28.44;UID=lims_user;",
" SELECT t1.SAMPLING_DATE,
t6.ITEM_NAME AS ITEM, t2.NUM_RESULT
FROM [dbo].SMP_RESULT t1
LEFT OUTER JOIN [dbo].UNIT t7
ON ( t7.UNIT_NO = t1.UNIT_NO )
LEFT OUTER JOIN [dbo].SMP_MASTER t3
ON ( t1.SMP_NO = t3.SMP_NO )
LEFT OUTER JOIN [dbo].ITEM_RESULT t2
ON ( t1.SMP_RESULT_NO = t2.SMP_RESULT_NO )
LEFT OUTER JOIN [dbo].SMP_CLASS t5
ON ( t3.SMP_CLASS_NO = t5.SMP_CLASS_NO )
LEFT OUTER JOIN [dbo].ITEM t6
ON ( t6.ITEM_NO = t2.ITEM_NO )
WHERE ( ( ( t1.SAMPLING_DATE between DATEADD(DAY, -7, GETDATE()) and GETDATE() ) AND ( t1.REQ_REMARK like 'Auto Request%' ) AND ( t2.NUM_RESULT is not null ) ) )
and SMP_CLASS_CODE = 'MONOMER'
and UNIT_NAME = 'CFU'
and SMP_CODE = 'SN1101'
and (ITEM_NAME like 'SP.GR%'
OR ITEM_NAME like 'Distillation, 70%'
OR ITEM_NAME like 'Distillation, 90%'
OR ITEM_NAME like 'Total Aromatics%'
OR ITEM_NAME like 'NP3%'
OR ITEM_NAME like 'NP4%'
OR ITEM_NAME like 'NP5%'
OR ITEM_NAME like 'NP6%'
OR ITEM_NAME like 'NP7%'
OR ITEM_NAME like 'NP8%'
OR ITEM_NAME like 'NP9%'
OR ITEM_NAME like 'IP4%'
OR ITEM_NAME like 'IP5%'
OR ITEM_NAME like 'IP6%'
OR ITEM_NAME like 'IP7%'
OR ITEM_NAME like 'IP8%'
OR ITEM_NAME like 'IP9%'
OR ITEM_NAME like 'Total Naphthenes%'
);"
);
dt_lims_tran = dt_lims << Transpose(
columns( :NUM_RESULT ),
By( :SAMPLING_DATE ),
Label( :ITEM )
);
Close( dt_lims, NoSave );
dt_lims_tran:SAMPLING_DATE << Data type( Numeric ) << Set Modeling Type( Continuous ) << Format("yyyy-mm-ddThh:mm:ss", 19, 0);
lastRow = N Rows(dt_lims_tran);
dt_lims_tran << Delete Rows(1::lastRow - 1);
// Column Summary
dt_lims_tran << New Column("T-NP of LIMS",
Numeric,
"Continuous",
Format("Best", 12),
Formula(
If(
Any(Is Missing(:NP3), Is Missing(:NP4), Is Missing(:NP5), Is Missing(:NP6), Is Missing(:NP7), Is Missing(:NP8), Is Missing(:NP9)), // Missing data filtering
., // Missing data filtering
Sum(:NP3, :NP4, :NP5, :NP6, :NP7, :NP8, :NP9) // Sum
)
)
);
// Column Summary
dt_lims_tran << New Column("T-IP of LIMS",
Numeric,
"Continuous",
Format("Best", 12),
Formula(
If(
Any(Is Missing(:IP4), Is Missing(:IP5), Is Missing(:IP6), Is Missing(:IP7), Is Missing(:IP8), Is Missing(:IP9)), //Missing data filtering
., // Missing data filtering
Sum(:IP4, :IP5, :IP6, :IP7, :IP8, :IP9) // Sum
)
)
);
// Delete Column
dt_lims_tran << Delete Columns( :Label );
//Column Head Change
dt_lims_tran:SAMPLING_DATE << Set Name( "DATE" );
start_time = FormatDatetime(Today() - InMinutes(5), "yyyy/m/d H:M:00");
end_time = FormatDatetime(Today() - InMinutes(0), "yyyy/m/d H:M:00");
tag_list = "'A0NIRCOND35', 'A0NIRCOND31', 'A0NIRCOND32', 'A0NIRCOND43', 'A0NIRCOND1', 'A0NIRCOND2', 'A0NIRCOND3', 'A0NIRCOND4', 'A0NIRCOND5', 'A0NIRCOND6', 'A0NIRCOND7',
'A0NIRCOND8', 'A0NIRCOND9', 'A0NIRCOND10', 'A0NIRCOND11', 'A0NIRCOND12', 'A0NIRCOND13', 'A0NIRCOND25'";
/*tag_list = { "H2TC41164", "H2FRC41053", "H2FRC41049", "H2AI43542_A", "H2AI43542_B", "H2AI43542_C", "H2AI43542_D", "H2AI43542_E", "H2AI43542_H" };
ConcatItems(tag_list, "', '");*/
PERIOD_time = "00:10:00";
// OIS Data
dt_tmp = Open Database(
"DSN=PLUTO;",
"SELECT CAST(TS AS CHAR FORMAT 'YYYY/MM/DD HH:MI:00') AS TREND_TIME, AVG AS IP_VALUE, NAME
FROM AGGREGATES
WHERE NAME IN (" ||tag_list|| ")
AND PERIOD = ("||PERIOD_time||")
AND TS >= CAST('" || start_time || "' AS TIMESTAMP FORMAT 'YYYY/MM/DD HH:MI:00')
AND TS <= CAST('" || end_time || "' AS TIMESTAMP FORMAT 'YYYY/MM/DD HH:MI:00')"
);
// Pivot
dt_tmp_tran = dt_tmp << Transpose(
columns( :IP_VALUE ),
By( :TREND_TIME ),
Label( :NAME )
);
Close(dt_tmp, NoSave);
dt_tmp_tran << New Column( "T-NP of OIS",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula( Sum( :A0NIRCOND1, :A0NIRCOND2, :A0NIRCOND3 , :A0NIRCOND4, :A0NIRCOND5, :A0NIRCOND6 , :A0NIRCOND7 ))
);
dt_tmp_tran << New Column( "T-IP of OIS",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula( Sum( :A0NIRCOND8, :A0NIRCOND9, :A0NIRCOND10 , :A0NIRCOND11, :A0NIRCOND12, :A0NIRCOND13 ) )
);
//== transpose
dt_tmp_tran << Select Where( :A0NIRCOND35< 0.5 | :A0NIRCOND35 > 1.0);
dt_tmp_tran << Delete Rows;
dt_tmp_tran << Select Where( :A0NIRCOND31< 80| :A0NIRCOND31> 200);
dt_tmp_tran << Delete Rows;
// DATE(TREND_TIME) Form Trans
dt_tmp_tran:TREND_TIME << Data type( Numeric ) << Set Modeling Type( Continuous ) << Format("yyyy-mm-ddThh:mm:ss", 19, 0);
// Filtering
dt_tmp_tran << Delete Columns( :Label );
//Change Column name
dt_tmp_tran:TREND_TIME << Set Name( "DATE" );
/*dt_to_concat = dt_tmp_tran << Join(
With( Data Table( dt_lims_tran ) ),
By Matching Columns( :TREND_TIME = :SAMPLING_DATE ),
Drop multiples( 1, 0 ),
Include Nonmatches( 1, 0 ),
Preserve main table order( 1 ),
Output Table( "dt_to_concat" )
)*/
// table 합치기
dt_to_concat = Data Table( dt_tmp_tran ) << Concatenate(
Data Table( dt_lims_tran ),
Output Table( "dt_to_concat" )
);
Close( dt_lims_tran, NoSave );
Close( dt_tmp_tran, NoSave );
//== add data
dt << Concatenate(dt_to_concat, Append to first table);
dt;