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

String Character Trim and conversion to date

All,

 

I am currently working with a table that contains a column with character strings like

 

'MQC_030817_1_Pb' or

'Cond_060917_1'.

 

I am trying to extract the string in between the first 2 delimeters '_ 030817_'and convert it into a date format

in order to sort the column by this date.

 

I appreciate any inputs on how to express a jsl formula to achieve this.

 

Thank you for your support in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Jeff_Perkinson
Community Manager Community Manager

Re: String Character Trim and conversion to date

Once again, the Word() function comes to the rescue.

JMPScreenSnapz122.png

 

 

Informat( Word( 2, :Column 1, "_" ) )

Starting from the inside, the Word() function returns the second word of the string using _ as a delimiter.

 

The Informat() function converts the string of numbers into the corresponding date value.

 

 

 

-Jeff

View solution in original post

5 REPLIES 5
Jeff_Perkinson
Community Manager Community Manager

Re: String Character Trim and conversion to date

Once again, the Word() function comes to the rescue.

JMPScreenSnapz122.png

 

 

Informat( Word( 2, :Column 1, "_" ) )

Starting from the inside, the Word() function returns the second word of the string using _ as a delimiter.

 

The Informat() function converts the string of numbers into the corresponding date value.

 

 

 

-Jeff

Re: String Character Trim and conversion to date

Thanks for your help. That generally worked.

However, it seems I have to speciffacally set the desired date format as it is interpreting some entries as 'amercian style'.

 

MQC_280817_Pb150 --> 28Aug2017

MQC_060917_Pb158_2 --> 09Jun2017 --> Should be 06Sep2017

 

How could I best unify this?

 

Thanks again for your input.

 

Lutz

 

Jeff_Perkinson
Community Manager Community Manager

Re: String Character Trim and conversion to date

In that case you can add an input format to the Informat() function.

JMPScreenSnapz123.png

 

Informat( Word( 2, :Column 1, "_" ), "ddmmyyyy" )
-Jeff

Re: String Character Trim and conversion to date

Thanks for this as I now have the table sorted by time.

Unfortunately it seems that the default sorting for the overlay legend in graph builders seems to be alphanumeric.

Is there any way to change the legend sorting to follow the sorting of the table.

 

(Same kind of question for X-axis of variablity charts which seems to be sorted alphanumeric rather based on order in table).

 

Thanks once more.

 

Lutz 

jerry_cooper
Staff (Retired)

Re: String Character Trim and conversion to date

There is a column property for this. Right-click on the header for your Overlay column and select Column Properties -> Row Order Levels. Do the same for your X-axis variable. I believe this will give you what you are looking for.