Transform a time column into a time interval column

[#1]

In my data table I have a categorical variable with ID settings that are time functions. I would like to use the functional data explorer to study the ID time functions; however the starting time of each ID setting is not constant. To use the FDE time intervals of each ID must be scaled starting from time = 0. How can I transform my time column into a time interval column so each ID setting starts at 0? Thanks for input.

[#2]

Without knowing how your categorical variable is formatted, it's difficult to suggest a solution. However, assuming that there is at least some numeric value in the column that can be extracted, you could use the Rank() function to scale things. I've attached a sample table to show how the formulas work.

[#3]

Thanks for input! Where do I find the formula Last?

[#4]

If you right click a column header then select New Formula Column, you'll see a list of the formulas that JMP will automatically make for you. For me, this is about 90% of all the formulas I build in JMP. To make the formula columns in the sample table, I used this, then modified the formulas a bit in the formula editor.

To use the formula editor, right click on the header and select Formula. There, you'll see a searchable list of all the possible formulas on the left. 

[#5]

Hi Jed, maybe it is better to specify with an example in attached data table what I need: in the example there are 3 ID settings ;next to there is a time column in seconds. In the time interval column, for each ID setting I need a time scale tarting from 0. How do I do this in JMP? Thanks in advance for help, I need to do this for 80 data files that will concatenated with each 3 to 10 ID settings.

[#6 · Accepted solution]

Thanks for the data file. Here are steps to accomplish this:

1. Right click Time column, select New Formula Column...Aggregate...Minimum
2. Right click new column, select Formula, change formula to include ID as a "By" variable (third argument)
   1. 
3. Rename this column to "Start Point"
4. Select both Time and Start Point columns, right click on column header, select New Column Formula...Combine...Difference

I added 2 files to show this: a workflow if you're on JMP 17, and the data table with script saved if you're on JMP < 17. 

[#7]

Thanks a lot Jed, this works fine!

[#8]

Have you considered the data processing capabilities built into FDE?

[#9]

Hi Mark, maybe it is better to specify with an example in attached data table what I need: in the example there are 3 ID settings ;next to there is a time column in seconds. In the time interval column, for each ID setting I need a time scale tarting from 0. How do I do this in JMP? Thanks in advance for help, I need to do this for 80 data files that will concatenated with each 3 to 10 ID settings.

[#10]

Thanks for sharing the example. The data processing tools in FDE won't help this case.