creating data table from min-max dates and making a spine of data

Question

I'm back from an 7 year hiatus from JMP scripting and I'm looking for some help to create a data table based on min and max dates and a list of things. I'm already importing the list of things from an excel file and it's in a data table, but what I need to do is take my min/max dates and combine them with my list of things by iterating on a 1 month interval from the min date to the max date (inclusive). I already have the min/max dates stored as table variables in m/y format.  Structurally, I'd like the data table to have two columns, Date, and ThingsList with the ThingsList repeated for each date. See below for an example of how I'd like the data to be structured.

mindate is "month1" in the example below with maxdate being "month3".  How can this be scripted? Appreciate the help!

thing1 Month1

thing2 Month1

thing3 Month1

thing1 Month2

thing2 Month2

thing3 Month2

thing1 Month3

thing2 Month3

thing3 Month3

Below is what i have so far, basically to define the min/max date and populate the data table.

New Window( "Set Date Range for Data Merge",
	<<Modal, 
	sd = Date Increment( Today(), "year", -2, "start" );
	ed = Today();
	H List Box(
		Text Box( "From:" ),
		scal = Number Edit Box( sd, <<Set Format( Format( "m/y" ) ), <<SetFunction( Function( {this}, sd = scal << Get ) ), <<Set Show Spin Box(1) ),
		Spacer Box( Size( 20, 20 ) ),
		Text Box( "To:" ),
		ecal = Number Edit Box( ed, <<Set Format( Format( "m/y" ) ), <<SetFunction( Function( {this}, ed = ecal << Get ) ), <<Set Show Spin Box(1) ),
	);
);

startdate = Format Date( sd, "m/y" );
enddate = Format Date( ed, "m/y" );

Show( startdate, enddate );

merge = New Table( "Merge" );
merge << New Column( "Month" );
merge << New Column ( "Things");

JM13 · Answer

Thanks! This worked great and i was able to adapt it perfectly.

JM13 · Answer

Thanks so much Jim! I'll give this a try and report back.

txnelson · Answer

Here is a script that takes the start and stop date and produces records for each month for each value of the things from the data table.

Names Default To Here( 1 );
New Window( "Set Date Range for Data Merge",
	<<Modal,
	sd = Date Increment( Today(), "year", -2, "start" );
	ed = Today();
	H List Box(
		Text Box( "From:" ),
		scal = Number Edit Box(
			sd,
			<<Set Format( Format( "m/y" ) ),
			<<SetFunction( Function( {this}, sd = scal << Get ) ),
			<<Set Show Spin Box( 1 )
		),
		Spacer Box( Size( 20, 20 ) ),
		Text Box( "To:" ),
		ecal = Number Edit Box(
			ed,
			<<Set Format( Format( "m/y" ) ),
			<<SetFunction( Function( {this}, ed = ecal << Get ) ),
			<<Set Show Spin Box( 1 )
		),

);
);

dt = Current Data Table(); // Point to the table with the list of Things
things = dt:Thing << get values;

numbMonths = Date Difference( sd, ed, "month" );

dtNew = New Table( "Output",
	New Column( "Thing" ),
	New Column( "Month-Year", Format( "m/y", 7 ) ),
	add rows( N Items( things ) * numbMonths )
);

theDay = Day( sd );
theMonth = Month( sd );
theYear = Year( sd );
theRow = 0;

For( monthCNT = 1, monthCNT <= numbMonths, monthCNT++,
	For( thingCNT = 1, thingCNT <= N Items( things ), thingCNT++,
		theRow++;
		:Thing[theRow] = things[thingCNT];
		:Name( "Month-Year" )[theRow] = Date MDY( theMonth, theDay, theYear );
	);
	If( theMonth < 12,
		theMonth = theMonth + 1,
		theMonth = 1;
		theYear = theYear + 1;
	);
);