Ciao, sono un utente di JMP Live.
Al momento sto utilizzando una semplice funzione all'interno di JMP per sfruttare la funzionalità di aggiornamento automatico in JMP Live per scopi di dashboard. Tuttavia, mentre la funzione funziona correttamente sul mio PC, non funziona correttamente su JMP Live.
Anche se ho disabilitato "Suppress Eval" prima di eseguire Data Publish, quando scarico i dati in seguito, la colonna in cui la funzione dovrebbe essere applicata appare vuota e vedo che "Suppress Eval" è di nuovo selezionato in Column Info. Questo problema si è verificato dall'ultimo aggiornamento della versione.
Apprezzerei se potessi indagare sulla causa di questo problema. Per riferimento, sto condividendo lo script di aggiornamento che sto attualmente utilizzando.
Grazie.
//==== 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;
