cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
qspringleaf
Level III

Question about "word" formula, purpose to split one character and get key shorten word

I have one table with one column, name is "Batch ID", I want to use one script to generate one column, name is "Short ID", formula use Word( 2, :Batch ID, "L22DD1" ), only want to get 2nd word, for example, E3L22DD1K27--->K27, but how come after run JSL, its outcome is K2 not K27? kindly help to sugget whether have other solutions if it not work?

 

Batch IDShort ID
M3L22DD1K2K2
E3L22DD1K45K45
M3L22DD1K41K4
E3L22DD1K27K27
M3L22DD1K3K3
M3L22DD1K42K42
M3L22DD1K43K43
M3L22DD1K44K44
M3L22DD1K45K45
M3L22DD1K4K4
M3L22DD1K5K5
M3L22DD1K6K6
E3L22DD1K46K46
E3L22DD1K47K47
E3L22DD1K48K48
E3L22DD1K49K49
E3L22DD1K50K50
E3L22DD1K51K51
E3L22DD1K52K52
1 ACCEPTED SOLUTION

Accepted Solutions
Thierry_S
Super User

Re: Question about "word" formula, purpose to split one character and get key shorten word

Hi,
The problem with using a string as delimiter is that the function Word() does not understand it a unique string but as a collection of individual delimiters. Hence, in your example where M3L22DD1K41 yields K4, the digit "1" in your delimiter is found in the string "L22DD1".
One way to solve this is to use the Substitute function within the Word function as follows:
Word (2, substitute (:BATCH_ID, "L22DD1", "-"),"-")
This way every occurence of "L22DD1" is replace within the formula by "-" and then the desired word is extracted based on the "-" delimiter
Hope it helps.
Best
TS
Thierry R. Sornasse

View solution in original post

5 REPLIES 5
txnelson
Super User

Re: Question about "word" formula, purpose to split one character and get key shorten word

Here is the formula that I would use in creating the Short ID column

New Column( "Short ID", character, formula( "K" || Word( -1, :Batch ID, "K" ) ) );

 

Jim
qspringleaf
Level III

Re: Question about "word" formula, purpose to split one character and get key shorten word

thanks sir, it works well if the name follow same rule.

but if one row content is changed as unexpected, for example "M3L22DD1X10_I35_O55", what I want is short name "X10_I35_O55", do have better solution.

then keep use "L22DD1"  separate the word to two parts and get the last one. M3L22DD1X10_I35_O55-->X10_I35_O55?

txnelson
Super User

Re: Question about "word" formula, purpose to split one character and get key shorten word

If you know what the break string is, i.e. L22DD1, then the 2 parts can be identified and separated with the following 2 assignment statements

For the Short ID

Substr( :Batch ID, Contains( :Batch ID, "L22DD1" ) + 6 )

For the First Part of the Batch ID

Substr( :Batch ID, 1, Contains( :Batch ID, "L22DD1" ) + 5 )
Jim
Thierry_S
Super User

Re: Question about "word" formula, purpose to split one character and get key shorten word

Hi,
The problem with using a string as delimiter is that the function Word() does not understand it a unique string but as a collection of individual delimiters. Hence, in your example where M3L22DD1K41 yields K4, the digit "1" in your delimiter is found in the string "L22DD1".
One way to solve this is to use the Substitute function within the Word function as follows:
Word (2, substitute (:BATCH_ID, "L22DD1", "-"),"-")
This way every occurence of "L22DD1" is replace within the formula by "-" and then the desired word is extracted based on the "-" delimiter
Hope it helps.
Best
TS
Thierry R. Sornasse
qspringleaf
Level III

Re: Question about "word" formula, purpose to split one character and get key shorten word

so thankful for help, it works well now and also understand more about function of  "word".