String Character Trim and conversion to date

[#1]

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.

[#2 · Accepted solution]

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

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.

[#3]

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

[#4]

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

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

[#5]

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 

[#6]

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.