Bonjour, je suis un utilisateur de JMP Live.
J'utilise actuellement une fonction simple dans JMP pour exploiter la fonction d'actualisation automatique de JMP Live à des fins de tableau de bord. Cependant, bien que la fonction fonctionne correctement sur mon PC, elle ne fonctionne pas correctement sur JMP Live.
Même si j'ai désactivé « Supprimer l'évaluation » avant d'exécuter la publication des données, lorsque je télécharge ultérieurement les données, la colonne dans laquelle la fonction doit être appliquée apparaît vide et je peux voir que « Supprimer l'évaluation » est à nouveau cochée dans les informations sur la colonne. Ce problème est survenu depuis la récente mise à jour de la version.
J'apprécierais que vous puissiez rechercher la cause de ce problème. Pour référence, je partage le script d'actualisation que j'utilise actuellement.
Merci.
//==== 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;
