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

[#1]

I was given a file similar to this

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]

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

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

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

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.

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

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

[#3]

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?

[#4]

This is an example table to use.  You are correct, I need to look at each row and get the codes for each date between the start and end dates.  It doesn't matter if there are duplicates.  Ultimately, I need to know if all the codes are the same, or if there are different codes in each time frame.  I need to know for each row if the codes that fall between the start and stop dates are all "2"s or all " 3"s or a combination of the codes.  

I think this must need to be some sort of script as I cannot think of a formula that will work.  I have no experience creating scripts so any help would be appreciated!!

[#5]

This can be most likely made much simpler with your final need in mind, but as there is no exact specification / result column I just created something I think should help you forward

Names Default To Here(1);

dt = Open("$DOWNLOADS/example table.jmp");
Try(Column(dt, "Date 1") << Set Name("date_1"));

date_cols = Filter Each({colname}, dt << Get Column Names("String"),
	Starts With(colname, "date_");
);
code_cols = Filter Each({colname}, dt << Get Column Names("String"),
	Starts With(colname, "code ");
);

dt << New Column("Result", Character, Multiple Response);

For Each Row(dt,
	dates = dt[Row(), date_cols];
	between_bin = :"Start date"n <= dates <= :"Stop date"n;
	between_idx = Loc(between_bin);
	If(N Items(between_idx) > 0,
		codes = dt[Row(), code_cols[between_idx]];
		valid = Loc(codes);
		If(N Items(valid) == 0,
			codes = {};
		,
			codes = As List(codes[valid`]);
		);
	,
		codes = {};
	);

	res = Transform Each({code}, codes,
		Char(code);
	);
	
	:Result = Concat Items(res, ", ");
);