cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Check out the JMP® Marketplace featured Capability Explorer add-in
Choose Language Hide Translation Bar

Creating a script to select only certain values related to specific date range

I was given a file similar to this

PruningTheSpark_0-1734151299506.png

I was asked to create a formula that pulls all the codes that happened between the start and stop dates.  I'm unsure how to set this up as a formula.  Is this something that could be created as a script to pull all the codes that occur between the start and stop dates?  I have never set up a script before and hoping someone can walk me through how to create one.

Thanks!

 

2 REPLIES 2
txnelson
Super User

Re: Creating a script to select only certain values related to specific date range

Welcome to the Community.

The actual statements that will select rows within a given date range and create a new data table with just those selected rows included.

// Select the row that have the date range desired
dt << select where( :Start Date >= informat("01/10/2025","m/d/y") & 
	:End Date <= informat("04/30/2025","m/d/y"));
// Create the new table
dtSubset = dt << subset( selected columns(0), selected rows(1), output table("theSubset"));

Here is a full example that first creates an example data table, and then runs the above lines to select and create the data table.

(Note:  Below example was somehow left out of original posting. Thanks to @jthi for heads up on the omission )

Names Default To Here( 1 );

// Create an Example data table
dt = New Table( "Example",
	Add Rows( 20 ),
	New Column( "Start Date",
		Format( "m/d/Y" ),
		set each value( Today() + In Days( Random Integer( 1, 100 ) ) )
	),
	New Column( "End Date",
		Format( "m/d/Y" ),
		set each value( :Start Date + In Days( Random Integer( 1, 50 ) ) )
	)
);

// Select the row that have the date range desired
dt << select where(
	:Start Date >= Informat( "01/10/2025", "m/d/y" ) & :End Date <=
	Informat( "04/30/2025", "m/d/y" )
);
// Create the new table
dtSubset = dt << subset(
	selected columns( 0 ),
	selected rows( 1 ),
	output table( "theSubset" )
);

Another interesting, interactive way to create the new table is to use a Data Filter. The Data Filter allows the user to interactively select rows in the data table, and once the desired rows are selected, a new table from those rows can be created.

To to this: Once the data table is displayed

txnelson_1-1734155158308.png

Go to the Rows pull down menu and select "Data Filter"

txnelson_2-1734155406295.png

In the Data Filter window, select the Start and End Dates, and Click on the + sign

txnelson_3-1734155523668.png

The Filters will then be displayed.

Click on the Show and Include check boxes.  The indicate the action to take on the rows in the data table when the setting for the filters select the rows.

txnelson_4-1734155694846.png

Now the Filters can be set to make the selection.  The blue lines can be dragged to the desired Star and End Dates, or the values can be directly typed in in the displayed date area for each filter.

Please note how the rows in the data table become selected as the filters are changed

txnelson_5-1734156015619.png

Once the desired rows are selected a new data table can be created by selecting the Show Subset selection from the Data Filter red triangle's list of actions.  Just click on the red triangle beside the Data Filter title and select the option

txnelson_6-1734156239580.png

txnelson_7-1734156306378.png

 

Jim
jthi
Super User

Re: Creating a script to select only certain values related to specific date range

Could you provide example table to make it a bit easier to create an example?

 

I wouldn't most likely create a formula for this but rather a script. I assume you wish to extract all code_x values in which date_x falls between start date and end date for each row? Where should they be recorded? What should be done to duplicates? Can data format be changed (could the data be stacked)?

 

 

@txnelson did JMP community drop your full example?

-Jarmo