cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
aliegner1
Level IV

Help doing a table transform and converting a timestamp?

so I've got a data table from a sensor that I am not quite sure how to get formatted correctly. It's a light sensor that outputs voltage readings.

 

The first column in the table is the wavelength measured and then there's 100+ columns for each time instance it measures data.

 

How would I go about transforming or pivoting this correctly? Would just a Table Transpose be the correct activity?

 

Next, advice on how to convert raw timestamp into a usable timeseries? It comes out looking like this.

DD-MM-YYYYTHH:MM:SS.###Z

what can I do to convert this into a usable timeseries value to plot as an x-axis? It's down to the thousandth of a second, 0.1s increments.

Bonus, how to do this to find the min and max and normalize the time to be 0-->max?

...
14-07-2020T22:47:31.400Z
14-07-2020T22:47:31.500Z
14-07-2020T22:47:31.600Z
14-07-2020T22:47:31.700Z
14-07-2020T22:47:31.800Z
14-07-2020T22:47:31.900Z
...

 

 

11 REPLIES 11
Jeff_Perkinson
Community Manager Community Manager

Re: Help doing a table transform and converting a timestamp?


@aliegner1 wrote:

Question about the timestamps: how would I convert this to a raw time duration? I'm seeing there's the "Date Difference" formula, but it looks like it needs two columns. How do I just make this a time series starting at 0.000s --> max time?

 

Before we go too much further I think we need to clarify this question here. @txnelson interpreted that as you wanting to get rid of the date portion of the datetime value. I don't think that's what you want.

 

You've now got a column of datetime values. That column can be used as a time series, it just won't start at 0.

 

If, instead, you'd rather a column that counted the time difference between consecutive rows of the data table that's easy to do with simple subtraction and row subscripting.

 

2020-09-09_11-59-26.691.png

 

I have a couple of things to point out here:

  • In the first row the value is set to 0.
  • I don't need to use the Date Difference() function because I'm interested in the values in secs. So, simple subtraction works.
  • Because of floating point arithmetic the difference in these values aren't precisely on the tenths of a second. I've formatted this column to display in tenths of a second, i.e. Fixed Dec with 1 decimal place.
  • Finally, but perhaps more complicated is that your original question said that you had multiple wavelengths. Do you want to restart this time sequence at 0 for each wavelength? If so, that's not a problem. It will just take a couple of changes to the formula.

2020-09-09_12-04-31.018.png

  • Now the formula checks for row 1 or for the lamda value to be different from the previous lambda (using the Lag() function) to set the sequence to 0.
  • It also uses Lag() to get the value of the sequence in the previous row and adds the difference in the time values between the current and previous rows.

I hope this helps clarify and provide a good direction.

 

 

 

 

-Jeff
aliegner1
Level IV

Re: Help doing a table transform and converting a timestamp?

Thank you Jeff. Your input on that final equation is great and removes the worry about a run that crosses midnight.