BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
Highlighted
NilD
Community Trekker

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. 

0 Kudos
1 REPLY 1

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
)
0 Kudos