Subscribe Bookmark RSS Feed

String Character Trim and conversion to date

lutz_eckart_pyr

Community Trekker

Joined:

Aug 31, 2016

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.

5 REPLIES
Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

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
lutz_eckart_pyr

Community Trekker

Joined:

Aug 31, 2016

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

Joined:

Jun 23, 2011

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

JMPScreenSnapz123.png

 

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

Community Trekker

Joined:

Aug 31, 2016

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

Joined:

Jul 10, 2014

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.