cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
phillby
Level I

Creating Data Table Automatically

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

            )

)

1 ACCEPTED SOLUTION

Accepted Solutions
pmroz
Super User

Re: Creating Data Table Automatically

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.

View solution in original post

2 REPLIES 2
pmroz
Super User

Re: Creating Data Table Automatically

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.

phillby
Level I

Creating Data Table Automatically

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