BookmarkSubscribe
Choose Language Hide Translation Bar
dfusco923
New Contributor

Scripting Question - Data set with many months of data. Want to create using script a subset of the main data table with a "selected" month of data

Could someone please help me figure this out?  I think the problem as to do with improper input/formatting of "Month".

 

I have a data set with one of the columns being "Month".  "Month" has the following column properties:

*  Numeric

* Ordinal

* Format m/y

* Input Format m/y

I'm trying to establish a variable up front in the script for any given month in the case "05/2019"

 

MONTH = "05/2019";

 

ERROR MESSAGE:

MONTH = "05/2019";

/*:

Column "Month" requires numeric values{109} in access or evaluation of 'Bad Argument'

 

 

 

Later in the script I am trying to use the variable I tried to established above in the below script to cull out that particular month of data:

 

MyList = {"MONTH"};
CT_5<< Select Where( (Contains(MyList, :Month)))
CT_5 << Invert Row Selection;
CT_5 << Delete Rows();

 

Error Message:

Send Expects Scriptable Object{109} in access or evaluation of 'Send' , CT_5 << /*###*/Select Where( Contains( MyList, :Month ) ) /*###*/

 

In the following script, error marked by /*###*/

MyList = {MONTH};

CT_5 << /*###*/Select Where( Contains( MyList, :Month ) ) /*###*/;

CT_5 << Invert Row Selection;

CT_5 << Delete Rows();

 

0 Kudos
7 REPLIES 7
txnelson
Super User

Re: Scripting Question - Data set with many months of data. Want to create using script a subset of the main data table with a "selected" month of data

I am a little confused on exactly what you need.  However, I think the code below might be helpful and if not exacly what you want, at least get the conversation going

// Your Month column is a JMP Date/Time column.  It is numeric, based upon
// the number of seconds since Jan. 1, 1904.  Therefore, your MONTH variable
// needs to have the number of seconds set.  The easiest way to do this is
// to use the Informat() function.
MONTH = Informat( "05/2019", "m/y" );

// If you show the MONTH variable using a Fixed Decimal format, it will show
// the numeric value
Show( Format( MONTH, "Fixed Dec", 12 ) );

// Concerning the Select Where, I think what you really want to do, is to 
// determine the Month for each of rows and then select those rows where
// column Month is the 5th month.  That code would be
CT_5 << Select Where( Month( :Month ) == 5 );
Jim
Highlighted
vince_faller
Super User

Re: Scripting Question - Data set with many months of data. Want to create using script a subset of the main data table with a "selected" month of data

It's most likely because of the scoping hierarchy. I'm guessing your column is also called "Month"?  

If you explicitly scope it 

 

::month = "05/2019"
// or
here:month = "05/2019"
//or just change the name
month_str = "05/2019"

I think it should work.  

Vince Faller - Predictum
dfusco923
New Contributor

Re: Scripting Question - Data set with many months of data. Want to create using script a subset of the main data table with a "selected" month of data

I'm still struggling with this. My data set has "Workdate" for example 01/01/2019 through, everyday year to date. It also has a "Month" column which is numeric ordinal (01/2019, 02/2019, 03/2019, etc.) Employee Workdate WW Month Total Hrs J. Doe 01/01/2019 1 01/2019 5 J. Doe 01/04/2019 1 01/2019 3 J. Doe 02/05/2019 6 02/2019 4 J Doe 02/06/2019 6 02/2019 2 ETC, ETC For the Data Set I'm trying to Cull out just the "Month" = 05/2019. So as suggested I set up a variable: ::MONTH = "05/2019"; Then in the JSL I try to use tha to cull the dataset, but I cant get it to work. MyList = {::MONTH}; CT_5 << Select Where( (Contains(MyList, :Month))); CT_5 << Invert Row Selection; CT_5 << Delete Rows(); ???

0 Kudos
txnelson
Super User

Re: Scripting Question - Data set with many months of data. Want to create using script a subset of the main data table with a "selected" month of data

If I am understanding what you are trying to do, I suggest:

CT_5 << select where( month(:Workdate) == 5 and year(:Workdate) == 2019 );
Jim
0 Kudos
dfusco923
New Contributor

Re: Scripting Question - Data set with many months of data. Want to create using script a subset of the main data table with a "selected" month of data

Thanks for your suggestion.  It makes sense to me but when I try run the script:

CT_5 << select where( month(:Workdate) == 5 and year(:Workdate) == 2019 );

 

I receive a "JMP Alert" that states:

 

"  Unexpected "and year".  Perhaps there is a missing "," or ")".   "

 

I tried various changes adding "(" etc. but could not get the JSL to run.

0 Kudos

Re: Scripting Question - Data set with many months of data. Want to create using script a subset of the main data table with a "selected" month of data

You can use the And() function or the operator (&) but not one literally in place of the other. So either use this form:

 

CT_5 << select where( month(:Workdate) == 5 & year(:Workdate) == 2019 );

 

Or use this form:

 

CT_5 << select where( And( month(:Workdate) == 5, year(:Workdate) == 2019 ) );
Learn it once, use it forever!
0 Kudos
dfusco923
New Contributor

Re: Scripting Question - Data set with many months of data. Want to create using script a subset of the main data table with a "selected" month of data

Thanks by trial and error I got the JSL to run using as you suggested:

CT_5 << select where( month(:Workdate) == 05 & year(:Workdate) == 2019 );

Now I'm trying to set up a variable "SELECTED MONTH" at the very begining of the JSL script that I would update manually something like (in this case):

SELECTED MONTH = month(:Workdate) == 05 & year(:Workdate) == 2019; 

  

NOTE:  I think something is wrong with this script because it is not providing the output I am expecting.  When i run script to: 

 

Show (SELECTED MONTH); 

 

I am getting " =.: " instead of:  month(:Workdate) == 05 & year(:Workdate) == 2019

 

Then in the JSL below I would like to run script something like:

CT_5 << select where( SELECTED MONTH );

The intent being that it is equivalent to:

CT_5 << select where( month(:Workdate) == 05 & year(:Workdate) == 2019 );

Any suggestiongs?

 

 

0 Kudos