topic Re: Help with Script for select specific rows in Community Discussions

Help with Script for select specific rows

lmoraga — Thu, 25 May 2017 16:58:49 GMT

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!

Re: Help with Script for select specific rows

MikeD_Anderson — Thu, 25 May 2017 21:52:52 GMT

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.

Re: Help with Script for select specific rows

lmoraga — Thu, 25 May 2017 23:42:33 GMT

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?

Re: Help with Script for select specific rows

MikeD_Anderson — Thu, 25 May 2017 23:52:16 GMT

What version of JMP are you running?

Re: Help with Script for select specific rows

lmoraga — Fri, 26 May 2017 15:20:59 GMT

JMP Pro 11.1.1

Re: Help with Script for select specific rows

MikeD_Anderson — Fri, 26 May 2017 15:28:16 GMT

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

Re: Help with Script for select specific rows

mela_ssa — Tue, 30 May 2017 15:37:38 GMT

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") );

Re: Help with Script for select specific rows

lmoraga — Thu, 01 Jun 2017 20:03:29 GMT

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.

Re: Help with Script for select specific rows

mela_ssa — Fri, 02 Jun 2017 13:40:32 GMT

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

) ;