59482_en-US

liliconcom

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. 

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
)