topic Re: A script works in JMP PRO but not in JMP LIVE to query data and add spec limits to JMP LIVE control chart in Discussions

A script works in JMP PRO but not in JMP LIVE to query data and add spec limits to JMP LIVE control chart

bjbreitling — Mon, 01 Apr 2024 21:22:39 GMT

Hi,

I wrote a script that 1) queries a table(myDT) to build a control chart on and 2) another table(specLimitsTable) and 3) a loop that takes data from the second table to set spec limits on table 1.

It works just fine in JMP PRO and steps 1) and 2) work fine in JMP LIVE but when I add the 3rd part it says failed to regenerate when I go to refresh my data.

The JSL that works in JMP PRO is below:

myDT = Open Database(
    "APP=JMP;DATABASE=BBAnalytics;DRIVER={SQL Server};Description=BB Analytics Data Warehouse;Trusted_Connection=Yes;UID=%_UID_%;WSID=SFX-JPCZ724;SERVER=SFX-DWA01;DBCNAME=SFX-DWA01;",
    "SELECT * FROM [dbo].[DDGSControlChartDataBPX]",
    "myDT"
);
specLimitsTable = Open Database(
    "APP=JMP;DATABASE=BBAnalytics;DRIVER={SQL Server};Description=BB Analytics Data Warehouse;Trusted_Connection=Yes;UID=%_UID_%;WSID=SFX-JPCZ724;SERVER=SFX-DWA01;DBCNAME=SFX-DWA01;",
    "SELECT * FROM [dbo].[PivotSpecLimitsMediansBPXF]",
    "specDTTest"
);

// Iterate over the rows of the spec limits table
For Each Row(specLimitsTable,
    // Get values from the current row
    column_name = Column(specLimitsTable, "Column_Name")[Row()];
    lsl = Column(specLimitsTable, "LSL")[Row()];
    usl = Column(specLimitsTable, "USL")[Row()];

    // Set spec limits for the corresponding column in the main data table
    
    col_ref = Column(myDT, column_name);
    Eval(
	Eval Expr(
		col_ref << Set Property(
			"Spec Limits",
			{LSL( Expr( lsl ) ), USL( Expr( usl ) ),
			Show Limits( 1 )}
		)
	)
););

Re: A script works in JMP PRO but not in JMP LIVE to query data and add spec limits to JMP LIVE control chart

MikeD_Anderson — Thu, 16 May 2024 10:52:50 GMT

A couple of things to try - First, change the Open Table() structure to New SQL Query(). Second, try using Manage Limits() with the "Save to Column Properties" message instead of the loop. You can find examples of both in the scripting index. Query Builder could also be used to create the query calls graphically. You can then use the Red Triangle Menu option to save out a finished script snippet.

Re: A script works in JMP PRO but not in JMP LIVE to query data and add spec limits to JMP LIVE control chart

Eric_Hill — Thu, 16 May 2024 12:50:45 GMT

I think the problem is your refresh script does not return a data table. If you add:

myDT;

to the end of your script, does that make it work in JMP Live?

Let me know.

Thanks,

Eric