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!
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
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, ", ");
);
Jarmo's code is spectacular. I enjoy seeing how he approaches and solves problems. But, for most of my life, I have been considered by my collogues, friends, spouse and children as being strange in thought. On good days, I am referred to as a "Nerd".
So given my status in life, I have found out that most people do not think the way I do.
@PruningTheSpark states
"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!!".
I believe that a majority of JMP users fall close to PruningTheSpark's position. PruningTheSpark may not think there is a non scripting solution to the problem, but JMP's interactive capabilities to manipulate one's data are really powerful. So below I have put together the interactive steps it takes to solve PruningTheSparks issue.
Starting with the sample data table that was provided, I did modify it slightly. Given the results from Jamo's script, it shows that none of the rows have more than one valid code. Given that more than one code is a potential, I added in code on row 2 that would show up for row 2 to have multiple codes.
The first task is to find all of the codes that fall within the Start Date and Stop Date. Since there are 6 different code columns, the easiest way to do this all at once is to stack the data .
Tables=>Stack
It is a Multiple series stack, staking the date and the code columns
Now I can get rid of all codes that have missing values since they will not figure into the results. So I selected one cell that has a missing value in the Data 2 column which contains all of the codes and then right click and choose Select Mating Cells
Which selects all of the rows that have missing values for column Data 2.
Right click in the RowState column and select Delete Rows
and you are left with only the rows that have codes
Now I need to determine which of these codes fall within the Start Date and the Stop Date. For this I am just going to create a new column, which I will call "in between" which has a formula of
which has a 1 for rows where the column Data (it contains the Date from the original table) has a 1 for being in the range and a 0 for being outside of the range
Using the same technique as when deleting the codes with a missing value, the rows with zeros can be selected and deleted
To clean up the table, some of the no longer needed columns can be deleted
Now the task is to get the codes back into single rows. To do this, a simple Split can be done
Tables=>Split
Now to combine the code columns into one column, a Column Utility called Combine Columns can be used. Just select the columns that are to be combined and then
Cols=>Utility=>Combine Columns
Which pops up an input box to fill in the name of the new column, and what delimiter to place between items, in this case, a ","
this results in the table having a new column
Clean up on the table by deleting the no longer needed columns gives
Now all that has to be done, is to move the results back into the original data table. To do this, the Update platform will do the trick. Click on the original table and
Tables=>Update
Fill in the table to get the update from, and select to match record_id of the original table with record_id from the Split table and click on OK and the original table will contain are solution
No script was used, only 2 simple formula columns were required.
Now, as one goes through these steps, JMP places in the log, the JSL required to create the interactive steps that were run. So one can simply copy those pieces of JSL into a script, and at the end have a script that when run will great the solution in the blink of an eye.
names default to here(1);
dt = data table( "Example Table" );
// Stack data table
// → Data Table( "Stacked" )
dtStacked = Data Table( "example table" ) << Stack(
columns(
:date_1, :code 1, :date_2, :code 2, :date_3, :code 3, :date_4, :code 4,
:date_5, :code 5, :date_6, :code 6
),
Number of Series( 2 ),
Output Table( "Stacked" )
);
wait(0);
// Delete selected rows
Data Table( "Stacked" ) << Select Where( Is Missing( :Data 2 ) ) << Delete Rows;
// Delete columns
Data Table( "Stacked" ) << Delete Columns( :Label, :Label 2 );
// New column: is between
Data Table( "Stacked" ) << New Column( "is between",
Numeric,
"Continuous",
Format( "Best", 12 )
);
// Change column formula: is between
Data Table( "Stacked" ):is between << Set Formula(
:Data >= :Start date & :Data <= :Stop date
);
// Delete selected rows
Data Table( "Stacked" ) << Select Where( :is between == 0 ) << delete rows;
// Delete column: is between
Data Table( "Stacked" ) << Delete Columns( :is between );
// Delete columns
Data Table( "Stacked" ) << Delete Columns( :Stop date, :Start date, :Data );
// Split data table
// → Data Table( "Split" )
Data Table( "Stacked" ) << Split(
Split By( :Data 2 ),
Split( :Data 2 ),
Group( :record_id ),
Output Table( "Split" ),
Sort by Column Property
);
// Combine columns
Data Table( "Split" ) << Combine Columns(
columns( :"1"n, :"2"n, :"3"n ),
Column Name( "Result" ),
Delimiter( "," )
);
// Delete columns
Data Table( "Split" ) << Delete Columns( :"1"n, :"2"n, :"3"n );
// Update data table
Data Table( "example table" ) << Update(
With( Data Table( "Split" ) ),
Match Columns( :record_id = :record_id )
);
One can also open a new workflow, turn on the recorder and step through the steps and you will end up with a workflow that can be used in the future to create the solution.
