Hi, I have a table containing ProductName and I want to generate the OUTPUT column. The OUTPUT column a concatenation of ProductID with Serial#. How do I accurately parse the Serial# and create the OUTPUT column? Serial # examples: * 07-08_13 means Serial# 07, 08, 13 * 09-12 means Serial# 09, 10, 11, 12 * 01-03_05-08 means Serial# 01, 02, 03, 05, 06, 07, 08 * 01_04_05-08 means Serial# 01, 04, 05, 06, 07, 08 * 09_11_13_14 means Serial# 09,11,13,14 ProductNameOUTPUTAABBCC_07-08_13AABBCC-07AABBCC_07-08_13AABBCC-08AABBCC_07-08_13AABBCC-13AABBCC_09-12AABBCC-09AABBCC_09-12AABBCC-10AABBCC_09-12AABBCC-11AABBCC_09-12AABBCC-12EEFFGG_02EEFFGG-02HHIIJJ_01-07HHIIJJ-01HHIIJJ_01-07HHIIJJ-02HHIIJJ_01-07HHIIJJ-03HHIIJJ_01-07HHIIJJ-04HHIIJJ_01-07HHIIJJ-05HHIIJJ_01-07HHIIJJ-06HHIIJJ_01-07HHIIJJ-07KKLLLMM_01-03_05-08KKLLLMM-01KKLLLMM_01-03_05-08KKLLLMM-02KKLLLMM_01-03_05-08KKLLLMM-03KKLLLMM_01-03_05-08KKLLLMM-05KKLLLMM_01-03_05-08KKLLLMM-06KKLLLMM_01-03_05-08KKLLLMM-07KKLLLMM_01-03_05-08KKLLLMM-08NNOOPP_01_04_05-08NNOOPP-01NNOOPP_01_04_05-08NNOOPP-04NNOOPP_01_04_05-08NNOOPP-05NNOOPP_01_04_05-08NNOOPP-06NNOOPP_01_04_05-08NNOOPP-07NNOOPP_01_04_05-08NNOOPP-08QQRRSS_09_11_13_14QQRRSS_09QQRRSS_09_11_13_14QQRRSS_11QQRRSS_09_11_13_14QQRRSS_13QQRRSS_09_11_13_14QQRRSS_14

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Parse string of a column and fill another column

cchueng

Hi,

I have a table containing ProductName and I want to generate the OUTPUT column. The OUTPUT column a concatenation of ProductID with Serial#. How do I accurately parse the Serial# and create the OUTPUT column?

Serial # examples:

07-08_13 means Serial# 07, 08, 13
09-12 means Serial# 09, 10, 11, 12
01-03_05-08 means Serial# 01, 02, 03, 05, 06, 07, 08
01_04_05-08 means Serial# 01, 04, 05, 06, 07, 08
09_11_13_14 means Serial# 09,11,13,14

ProductName	OUTPUT
AABBCC_07-08_13	AABBCC-07
AABBCC_07-08_13	AABBCC-08
AABBCC_07-08_13	AABBCC-13
AABBCC_09-12	AABBCC-09
AABBCC_09-12	AABBCC-10
AABBCC_09-12	AABBCC-11
AABBCC_09-12	AABBCC-12
EEFFGG_02	EEFFGG-02
HHIIJJ_01-07	HHIIJJ-01
HHIIJJ_01-07	HHIIJJ-02
HHIIJJ_01-07	HHIIJJ-03
HHIIJJ_01-07	HHIIJJ-04
HHIIJJ_01-07	HHIIJJ-05
HHIIJJ_01-07	HHIIJJ-06
HHIIJJ_01-07	HHIIJJ-07
KKLLLMM_01-03_05-08	KKLLLMM-01
KKLLLMM_01-03_05-08	KKLLLMM-02
KKLLLMM_01-03_05-08	KKLLLMM-03
KKLLLMM_01-03_05-08	KKLLLMM-05
KKLLLMM_01-03_05-08	KKLLLMM-06
KKLLLMM_01-03_05-08	KKLLLMM-07
KKLLLMM_01-03_05-08	KKLLLMM-08
NNOOPP_01_04_05-08	NNOOPP-01
NNOOPP_01_04_05-08	NNOOPP-04
NNOOPP_01_04_05-08	NNOOPP-05
NNOOPP_01_04_05-08	NNOOPP-06
NNOOPP_01_04_05-08	NNOOPP-07
NNOOPP_01_04_05-08	NNOOPP-08
QQRRSS_09_11_13_14	QQRRSS_09
QQRRSS_09_11_13_14	QQRRSS_11
QQRRSS_09_11_13_14	QQRRSS_13
QQRRSS_09_11_13_14	QQRRSS_14

Accepted answers

txnelson

It just had a minor error where a -1 was not removed when I restructured my code.

Names Default To Here( 1 );
dt = Current Data Table();

dt2 = New Table( "The Output",
	New Column( "Product Name", character ),
	New Column( "ProductID", Character ),
	New Column( "OUTPUT", Character )
);

For( dtRow = 1, dtRow <= N Rows( dt ), dtRow++,
	If( dtRow == 1 | dt:ProductName[dtRow - 1] != dt:ProductName[dtRow],
		prodID = Word( 1, dt:ProductName[dtRow], "_" );
		remainder = Substr( dt:ProductName[dtRow], Length( prodID ) + 2 );
		While( Word( 1, remainder, "_" ) != "",
			theWord = Word( 1, remainder, "_" );
			If( Contains( theWord, "-" ),
				numDigets = Length( Word( 1, theWord, "-" ) ) - 1;
				zeros = Repeat( "0", numDigets );
				For( loop = Num( Word( 1, theWord, "-" ) ),
					loop <= Num( Word( 2, theWord, "-" ) ), loop++,
					dt2 << Add rows( 1 );
					theRow = N Rows( dt2 );
					dt2:ProductName[theRow] = dt:ProductName[dtRow];
					dt2:ProductID[theRow] = Word( 1, dt:ProductName[dtRow], "_-" );
					dt2:output[theRow] = dt2:ProductID[theRow] || "-" ||
					Substr( zeros, Length( Char( loop ) ) ) || Char( loop );
				);
			,
				dt2 << Add Rows( 1 );
				theRow = N Rows( dt2 );
				dt2:output[theRow] = prodID || "-" || Word( 1, theWord, "-" );
				dt2:ProductName[theRow] = dt:ProductName[dtRow];
				dt2:ProductID[theRow] = Word( 1, dt:ProductName[dtRow], "_-" );
			);
			remainder = Substr( remainder, Length( theWord ) + 2 );
		);
	)
);

Please make sure you take the time to study the code, so you completely understand how it is working.

All comments

jthi

Most likely you could write a formula with Lag to do that (or script with JSL), but you can also attempt to do that interactively but it takes quite a few steps:

1. Select ProductName column

2. Go to Cols/Utilities/Text To columns and use both - and _ as delimiters

3. Go to Tables and Stack and add everything else besides first column to Stack Columns selection

5. Remove Label column

6. Select both columns which are left and go to Rows / Row Selection / Select Dublicate Rows and delete those rows

7. Select one cell with empty value and choose Select Matching Cells and delete those rows

8. Now choose both colums and right click on the column header and create formula

9. Open the just created formula and change the , to _

10. Rename and delete columns/formulas

11. If you need to have ProductName there, you should be able to keep it there if you don't accidentally delete it in first step like i did

If you have to have - or _ depending on the serialnumber you could handle it in the concatenate formula most likely

Edit: I checked the question a bit more accurately and this is a bit more complicated than I showed here. You will have to add calculations for some of the serial numbers because they are between numbers. This solution won't work in those because some rows will be removed when duplicates are removed.

Chris_Kirchberg

Hi @cchueng ,

There are a couple of ways. Some are scripting base (look for a reply from someone suggesting this with and example) or you could use successive rounds of Text to Columns (example found here Solved: How can I split text in a column into multiple columns? - JMP User Community) Then you could concatenate the desired columns by selecting them and then either use a column formula or use the New Formula Column option after right clicking on one of the selected columns. Look for the Character options for concat. The column formula is more flexible and gives you a lot of options see the help on this topic (Create Formulas in JMP)

I hope that gives you some direction.

Best,

Chris_Kirchberg

@jthi ,

You beat me to it. Your explanation is more detailed and what I was thinking of too.

Chris_Kirchberg

Oh, your right. the - is interpreted as a range and the _ is interpreted as a true delimiter of items

cchueng

Thanks but I prefer a jsl rather than the interactive delimiter. Your method did not address the scenario when the Serial # 09-12. It should output rows like below. Not just AABBCC-09 and AABBCC-12. The "-" means "to" meaning range. So 09-12 means 09, 10, 11, 12.

AABBCC_09-12	AABBCC-09
AABBCC_09-12	AABBCC-10
AABBCC_09-12	AABBCC-11
AABBCC_09-12	AABBCC-12

cchueng

Hi Chris,

I have a script that will address some of the ProductName and sometimes will parse wrongly. Can you review and add codes to address those ProductNames that are not working so far?

Will work for below examples :

EEFFGG_02

HHIIJJ_01-07

KKLLLMM_01-03_05-08

Will not work yet for below examples :

AABBCC_07-08_13

NNOOPP_01_04_05-08

QQRRSS_09_11_13_14

dt=current data table();

(dt << New Column( "ProductID", Character )) << Set Selected;
(dt << New Column( "OUTPUT", Character )) << Set Selected;
 
//Parse string in ProductName column 

For Each Row(
    dt,
    //Assign the ProductID value
    :ProductID = Word( 1, :ProductName, "_-" ); //"_"
 
    //If the ProductName changes, capture the start and end
    If( :ProductName != Lag( :ProductName, 1 ),
       
        start = Num( Word( 2, :ProductName, "_-" ) );
        end = Num( Word( 3, :ProductName, "_-" ) );
            
    );
    
  //Assign the OUTPUT value
    :OUTPUT = :ProductID || "-" || Repeat( "0", 2 - Length( Char( start ) ) ) || Char( start );
    
    //After end value & there is an _, set start to value after _
    If( start == end & Contains( :ProductName, "_"),
        start = Num( Word(-2, :ProductName, "_-" ) ),
        start++);
);

DataTable 2.jmp

txnelson

Here is a script that produces a new table that expands the rows as requested. By placing the data into a new table, it bypasses the issue where there are not a sufficient number of rows for the expansion. It does appear to handle all of the known patterns.

Names Default To Here( 1 );
dt = Current Data Table();

dt2 = New Table( "The Output",
	New Column( "Product Name", character ),
	New Column( "ProductID", Character ),
	New Column( "OUTPUT", Character )
);

For( dtRow = 1, dtRow <= N Rows( dt ), dtRow++,
	If( dtRow == 1 | dt:ProductName[dtRow - 1] != dt:ProductName[dtRow],
		prodID = Word( 1, dt:ProductName[dtRow], "_" );
		remainder = Substr( dt:ProductName[dtRow], Length( prodID ) + 2 );
		While( Word( 1, remainder, "_" ) != "",
			theWord = Word( 1, remainder, "_" );
			If( Contains( theWord, "-" ),
				numDigets = Length( Word( 1, theWord, "-" ) ) - 1;
				zeros = Repeat( "0", numDigets );
				For( loop = Num( Word( 1, theWord, "-" ) ) + 1,
					loop <= Num( Word( 2, theWord, "-" ) ), loop++,
					dt2 << Add rows( 1 );
					theRow = N Rows( dt2 );
					dt2:ProductName[theRow] = dt:ProductName[dtRow];
					dt2:ProductID[theRow] = Word( 1, dt:ProductName[dtRow], "_-" );
					dt2:output[theRow] = dt2:ProductID[theRow] || "-" ||
					Substr( zeros, Length( Char( loop ) ) ) || Char( loop );
				);
			,
				dt2 << Add Rows( 1 );
				theRow = N Rows( dt2 );
				dt2:output[theRow] = prodID || "-" || Word( 1, theWord, "-" );
				dt2:ProductName[theRow] = dt:ProductName[dtRow];
				dt2:ProductID[theRow] = Word( 1, dt:ProductName[dtRow], "_-" );
			);
			remainder = Substr( remainder, Length( theWord ) + 2 );
		);
	)
);

cchueng

Thanks Jim but the OUTPUT appears to be incorrect. For example the first 3 lines, the OUTPUT should be :

AABBCC-07

AABBCC-08

AABBCC-13