Creating Data Table Automatically

[#1]

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] )

            )

)

[#2 · Accepted solution]

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.

[#3]

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