Creating Data Table Automatically

phillby — Wed, 28 Sep 2011 21:37:46 GMT

I am trying to create a table automatically but am a little stuck with Set Values.

I have opened the database using a SQL query, then copied the script from the table. In this script (below) I am selecting data from a packhouse, ALBS. This script works fine for this packhouse, but I want to automate this process to be able to open data from other packhouses. Clearly, if I open another packhouse, e.g. BBSS, using this script, then the line Set Values( {"21295131", "21295131", "21295131", "21295131", "21295131"} ) will not be relevant to the new data set. What command do I need to replace this Set Values line with to fetch the correct data from the database I've just opened. I've had a look through the book and Get Values seems like it might be heading in the right direction but I can't understand how to use this command.

Any help will be greatly appreciated, I've only been using JMP for a week so am very new to it all.

New Table( "TB_RAW_DATA",

Add Rows( 5 ),

New Table Variable( "SQL",

"SELECT top 5 PalletID, dtTempSample, TempSampleValue FROM dbo.tb_Raw_Data WHERE tb_Raw_Data.SiteName =

'ALBS'

AND tb_Raw_Data.ShipmentID = 'warehouse' order by palletid, dttempsample

New Table Variable( "JSL",

"Open Database(\!"DSN=TRG-SQL13;UID=;Trusted_Connection=Yes;APP=JMP;WSID=MTMLAP160;DATABASE=ZS_Export\!", \!"SELECT top 5 PalletID, dtTempSample, TempSampleValue FROM dbo.tb_Raw_Data WHERE tb_Raw_Data.SiteName =

'ALBS'

AND tb_Raw_Data.ShipmentID = 'warehouse' order by palletid, dttempsample

\!");"

New Column( "PalletID",

Character,

Nominal,

Set Values( {"21295131", "21295131", "21295131", "21295131", "21295131"} )

New Column( "dtTempSample",

Numeric,

Continuous,

Format( "d/m/y h:m:s", 22, -1 ),

Input Format( "d/m/y h:m:s", -1 ),

Set Values( [3387391680, 3392287200, 3394177560, 3394181160, 3394184760] )

New Column( "TempSampleValue",

Numeric,

Continuous,

Format( "Best", 12 ),

Set Values( [18.3, 15.3, 1.3, 1, 1] )

)

Re: Creating Data Table Automatically

pmroz — Thu, 29 Sep 2011 13:44:37 GMT

You need to use the SQL and JSL variables to build a dynamic set of JSL commands. For example:

packhouse = "ALBS";

dsn_string =

"DSN=TRG-SQL13;UID=;Trusted_Connection=Yes;APP=JMP;WSID=MTMLAP160;DATABASE=ZS_Export";

sql_string = "SELECT top 5 PalletID, dtTempSample, TempSampleValue " ||

" FROM dbo.tb_Raw_Data " ||

" WHERE tb_Raw_Data.SiteName = '" || packhouse || "' " ||

" AND tb_Raw_Data.ShipmentID = 'warehouse' " ||

"order by palletid, dttempsample";

dt = open database(dsn_string, sql_string, packhouse);

This will open the same dataset you have now, into a JMP table called ALBS. Substitute a different value for packhouse to read a different packhouse from your database.

Creating Data Table Automatically

phillby — Thu, 29 Sep 2011 19:40:20 GMT

Thanks for this, it appears to be doing what I want it to do.

topic Creating Data Table Automatically in Discussions

Creating Data Table Automatically

Re: Creating Data Table Automatically

Creating Data Table Automatically