World Statistics Day was yesterday, but we’re celebrating all week long! This celebration means acknowledging the impact statistics has on our world. Who is your favorite statistician? Share with us who they are and why they top your favorites list.
Choose Language Hide Translation Bar
Highlighted
NilD
Level II

Combining Multiple Columns with specific formatting

Hi,

I am stuck on a concatenate issue for two columns.

 

I have a full date in Column1 and a Unique Lot number in Column2. My goal is to combine column 1 and 2 with specific information. Here's example of what i have any what im looking for. I am using JMP 13, and was placing a formula directly into the column property instead of JMP Script. 

 

Column1

Column2

Results(format i am looking for)

 

2016/01/22 12:00 AM

12 

16-01-0012

2015/06/23  01:00 AM 

132

15-06-0132

2017/05/24   02:00 AM

9283 17-05-9283

 

 

I am able to combine three column using: 

 

Year(Column1)||Month(Column2)||(Results)

 

The result i get is: [2016 1 12]


Which isnt exact format of interest. 

 

I am looking to add the formula directly into a column instead of script. Any help would be appriciated. 

1 REPLY 1
Highlighted

Re: Combining Multiple Columns with specific formatting

Here is one solution to your column formula using the Substr, Length and Concatentate functions in the formula editor. The format for Substr is ( column name, starting location, length of substring). 

 

Substr( :Column 1, 3, 2 ) || "-" || Substr( :Column 1, 6, 2 ) || "-" ||
If(
	Length( :Column 2 ) == 2, "00" || :Column 2,
	Length( :Column 2 ) == 3, "0" || :Column 2,
	:Column 2
)
Article Labels

    There are no labels assigned to this post.