topic Re: creating data table from min-max dates and making a spine of data in Discussions

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

JM13 — Sat, 10 Jun 2023 23:18:36 GMT

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");

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

txnelson — Fri, 04 Sep 2020 15:00:53 GMT

It is pretty tough to provide any coding assistants without seeing your imported Excel file(s). Would you please provide a sample of your input file that has the table variables, etc.?

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

JM13 — Wed, 09 Sep 2020 17:47:54 GMT

Attached are basic examples of what my input table would look like and what i'd like to try to script to happen. In the example I'm showing in "desired output.jmp" it has a startdate and enddate of 7/2020 and 11/2020 respectively that would be determined via the JSL in my previous post. Since my first post I've sort of figured out how to at least create a data table of dates using this JSL. The only problem is that the interval of one month (in seconds) doesn't quite work since all months are not the same length. There has to be a more elegant way to do this. Anyway, any help on how to take the input table and use the date range script arrive at the "desired output.jmp" would be greatly appreciated. I'm kind of lost.

test = New Table( "test" );
test << New Column("test", Numeric, Continuous, Set Values(sd::ed::2628000));

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

txnelson — Thu, 10 Sep 2020 02:08:42 GMT

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;
	);
);

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

JM13 — Mon, 14 Sep 2020 17:44:37 GMT

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

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

JM13 — Tue, 15 Sep 2020 21:37:54 GMT

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