299058_en-US

liliconcom

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

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></MODAL>

 

Data TableScriptingRe: creating data table from min-max dates and making a spine of dataIt 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

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