topic Re: String Character Trim and conversion to date in Discussions

String Character Trim and conversion to date

lutz_eckart_pyr — Fri, 08 Sep 2017 08:20:46 GMT

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.

Re: String Character Trim and conversion to date

Jeff_Perkinson — Fri, 08 Sep 2017 12:05:52 GMT

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.

Re: String Character Trim and conversion to date

lutz_eckart_pyr — Fri, 08 Sep 2017 12:24:54 GMT

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

Re: String Character Trim and conversion to date

Jeff_Perkinson — Fri, 08 Sep 2017 12:37:09 GMT

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

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

Re: String Character Trim and conversion to date

lutz_eckart_pyr — Fri, 08 Sep 2017 13:24:40 GMT

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

Re: String Character Trim and conversion to date

jerry_cooper — Fri, 08 Sep 2017 15:02:06 GMT

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.