Subscribe Bookmark RSS Feed

Help with Script for select specific rows

lmoraga

New Contributor

Joined:

May 25, 2017

Hi all; I hope ypu are doing great...

 

I'm wonderig if somebody can helpme with this:

 

I have a Data Table wich minitoring VAV (Variable air volume box) temperature every 5 minutes, and  Table contains data from two weeks of monitoring (from sunday to saturday, from 12:00am to 11:55pm every day).

I need to develop a Script for select and create a new table but only whit data collected on bussines days, I mean, only the data what was collected from Monday to Friday, from 8:00am to 6:00pm.

 

Really thaks for any help!

 

8 REPLIES
M_Anderson

Staff

Joined:

Nov 21, 2014

Try using the JMP Table Query Builder (Under the table menu of JMP).  You can set up filters and generate a new table.  It writes the code for you and saves the query as a separate file if you want to rerun later.  

 

M

lmoraga

New Contributor

Joined:

May 25, 2017

Thanks for the help.

I was trying and reading about, but I couldn't enable the Query Builder function. Can you tell me how to enable it?

M_Anderson

Staff

Joined:

Nov 21, 2014

What version of JMP are you running?
lmoraga

New Contributor

Joined:

May 25, 2017

JMP Pro 11.1.1

M_Anderson

Staff

Joined:

Nov 21, 2014

That’s why. Query Builder is a feature in JMP 13. If you are on our standard license model you should be able to upgrade. You might want to call your license administrator about getting upgraded.

M
mela_ssa

Community Trekker

Joined:

Dec 17, 2013

Let your Start Date and time be May 15, 2017, 08:00:00 AM, and

End date and time be May 19, 2017, 06:00:00 PM.

You may be able to use the MDYHMS () function to select rows of your data table between the start and end dates.

For example

sdt = Current Data Table(); // setting the open data table as current

sdt << Select Where(

            (MDYHMS("05/15/2017 08:00:00 AM") <= :Date and Time <= MDYHMS ("05/19/2017 06:00:00 PM") )

            );

// subset selected rows and output to a new data table

Outdt = sdt << Subset( Output Table Name ("Output Date with selected Dates") );

lmoraga

New Contributor

Joined:

May 25, 2017

Hi mela_ssa, thanks a lot for your input.

 

Do you know if it’s possible modify this line:

 

sdt = Current Data Table(); // setting the open data table as current

sdt << Select Where(

            (MDYHMS("05/15/2017 08:00:00 AM") <= :Date and Time <= MDYHMS ("05/19/2017 06:00:00 PM") )

            );

 

To avoid selection by days?. This because for my just is important evaluate data between 8:00 AM to 6:00 PM, no matter the day.

mela_ssa

Community Trekker

Joined:

Dec 17, 2013

The days was needed as part of the argument for the MDYHMS function to work.

You could try the following example,

 

sdt << Select Where(

            (:Date and Time =>  08:00:00 )  & (:Date and Time <= 18:00:00 )

            );

 

Hopefully, JMP will interpret the :Date and Time variable correctly in the row selection. Otherwise,  you could use multiple conditions for several dates in the time range you want. For example,

Sdt << Select Where(

          ( :Date and Time => Informat( “15May2017:08:00:00”, “MDYHMS”)  ) & (:Date and Time <= Informat( “19May2017:06:00:00”, “MDYHMS”) ) or

            ( :Date and Time => Informat( “22May2017:08:00:00”, “MDYHMS”)) & (:Date and Time <= Informat( “26May2017:06:00:00”, “MDYHMS”) ) or

           ( :Date and Time => Informat( “29May2017:08:00:00”, “MDYHMS”) ) & (:Date and Time <= Informat( “02Jun2017:06:00:00”, “MDYHMS”) ) or

     /* other future dates through to Jul29 2017 */

          ( :Date and Time => Informat( “24July2017:08:00:00”, “MDYHMS”) ) & (:Date and Time <= Informat( “28July2017:06:00:00”, “MDYHMS”) )

) ;